Здравствуйте!

Такая ситуация (всё упрощено до минимума до сути) - есть таблицы (заказы / заказчики) :
CREATE TABLE customers ([ID] INTEGER PRIMARY KEY NOT NULL , name );
CREATE TABLE orders ([ID] INTEGER PRIMARY KEY NOT NULL , customer ); //тут несколько десятков тысяч записей.
для orders по полю customer построил индекс.

запрос - посчитать кол-во заказов по клиентам
sql : select name, (select count(*) from orders where customer = c.id ) from customers c
план:
SCAN TABLE customer
EXECUTE CORRELATED SCALAR SUBQUERY 1
SCAN TABLE orders AS td USING COVERING INDEX ind_orders_customer (customer=?)

время выполнения примерно 10сек

теперь в дизайнере таблицы меняю orders с указанием для колонки customer типа INTEGER, так: CREATE TABLE orders ([ID] INTEGER PRIMARY KEY NOT NULL , customer INTEGER );
тот же запрос, но план:
SCAN TABLE customer
EXECUTE CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE orders AS td USING COVERING INDEX ind_orders_customer (customer=?)
время выполнения становится - 50мс!!!!

вот зе фак???
почему такое изменение всего лиш declared type-а для колонки приводит к такой разнице во времени выполнения?
в чем тут дело?
объясните плиз кто понимает....

PPAА когда таблица была создана без указания типа, вы какой операцией туда записывали данные внешнего ключа?

Версия:
Может поле customer биндилось как text в результате чего так и ложилось
а потом при работе sqlite выполнял преобразование поля к integer или наоборот к строке.
но когда вы явно указали тип колонки - то преобразование выполнилось уже в момент заполнения...


в обоих случаях в колонке хранятся числа. проверено.

White OwlЕсли мы заранее не знаем сродство, то индекс вынужденно строится по полю вариантного типа. А значит каждая запись в индексе при каждой операции поиска будет приводиться к покрывающему типу (и критерий поиска тоже, конечно).
Если сродство известно заранее, то запись в индексе приводится к заявленному типу при вставке/обновлении данных. А во время операции поиска к заявленному типу приводится уже только критерий поиска.
не может же это давать настолько большую разницу... на 3 порядка!!!

да, ещё , если просто делать select * from orders where customer=x
то в плане
SEARCH TABLE orders USING INDEX ind1 (customer=?)
и всё летает милисекунды и в случае и без declared type у колонки.
так что явно дело не в этом, не в "приводиться к покрывающему типу"....