Вчера открих един ефект при SQLite, който се среща рядко, но може сериозно да намали производителността на заявките.
Ето тестов пример:
create table t1(a text unique);
create table t2(b integer);
explain query plan
select
*
from
t2 left join t1 on a = b;
Това дава следния план, въпреки, че на t1.a има прекрасен индекс (щото е UNIQUE), той не се използва:
SCAN TABLE t2
SCAN TABLE t1
Предполагам, че това се дължи на това, че оптимизаторът, като види, че b е integer, преобразува и a в integer, а съществуващият индекс е текстов.
За да бъде използван този индекс, заявката следва да бъде написана така:
select
*
from
t2 left join t1 on a = cast(b as text);
Тогава планът е следния:
SCAN TABLE t2
SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)
И съответно скоростта на заявката е сериозно по-висока.
Малко ми е трудно да кажа дали това е бъг или особеност на оптимизатора на заявките, тъй като типовете в SQLite са само пожелания, но не и твърдо правило, а в едно поле на таблиците могат да се записват данни от произволен тип. Тоест, оптимизаторът следва на има това предвид и да използва най-добрият вариант на заявката.
Ще питам DRH и ако има някакво обяснение ще пиша в темата.
Особеността е по-скоро се ползва само 1 индекс, т.е. той може да не е най-оптималният. Затова може да ползваш в заявката
INDEXED BY <индекса>
Не, това беше първото, което опитах. Но не работи. По две причини. Първо, индексът на unique поле е автоматичен и затова не му знаеш името.
Разбира се, можеш да го намериш или да създадеш явен индекс, но това ще е текстов индекс, а в израза a = b явно и двете страни се преобразуват в integer.
Съответно, в резултат дава грешка: no query solution;
Някои неща се изясниха, в резултат на обсъждането на форума на SQLite.
Номерът се състои в това, как SQLite каства изразите. Реално в SQLite няма типове данни, а има т.н. affinity - афинитет, сходство. Тоест, ако някакво поле е обявено като integer, то в него може да се запише и стринг и блоб, но базата предполага, че в него има записано число и си планира заявките от това предположение.
Също може да има полета и без афинитет.
Та така, когато има израз от вида a = b, то двете страни трябва да се приведат към еднакъв тип. И тогава се гледат афинитетите на величините от двете страни на сравнението.
Ако едната страна има числен афинитет, то всичко се преобразува към него. Ако едната страна няма афинитет, то всичко се преобразува към другата.
Възможен е и трети вариант, в който нищо не се преобразува – когато никоя от двете величини няма никакъв афинитет. Как става сравнението в този случай на мене не ми е много ясно, но документацията твърди именно това.
Тоест във въпросният пример, става следното – лявата и дясната страна на сравнението се преобразуват в числен вид, тъй като едното поле е обявено като integer. И тъй като индексът на другото поле е текстов, то той не може да се използва.
А когато полето с числов афинитет се преобразува явно в text, то и афинитета му се променя и сравнението става във текстова форма, в която може да се използва и съществуващия индекс.
Лично според мене, големият проблем е, че всичко това е доста неинтуитивно. Но, каквото такова.
Поуката е, че трябва често да се използва explain query plan и да се обмислят внимателно резултатите. 
аз лично не бих си помислил да джойнвам по полета от различен тип
Да, ама в SQLite въобще няма типове на полетата. Там не е проблем в едно поле да запишеш каквито и да е данни и те няма да бъдат преобразувани, а ще се запишат в таблицата такива, каквито са.
В този смисъл, всеки джойн е по полета с различен тип.