У меня есть таблица с несколькими сотнями тысяч строк. (Это предварительно вычисленная таблица, выражающая связь между леммами слов и другими большими таблицами.) Мне нужно сделать несколько выборок, чтобы найти комбинацию разных записей, т.е. мне нужно использовать «AS», чтобы выбрать… из ltc как l0, ltc как l1, ltc как l2… упорядочить по ... Скорость запроса зависит от сортировки: без сортировки это несколько миллисекунд, а с сортировкой это может занять несколько минут. Насколько я могу судить, это связано с тем, что временное B-дерево, которое Sqlite создает для сортировки, даже если у меня есть индекс в отсортированном столбце «nr». Я не понимаю, почему Sqlite не использует этот индекс.

CREATE TABLE ltc
(nr INTEGER, lemId INTEGER, cId INTEGER, bId INTEGER,
-- UNIQUE (lemId, cId, bId), 
-- if I add this uniqueness constraint, strangely enough it doesn’t use my index at all, even at a simple ORDER BY.
PRIMARY KEY(nr,lemId,cId),
FOREIGN KEY(lemId) REFERENCES lems(rowid),
FOREIGN KEY(cId) REFERENCES cs(rowid),
FOREIGN KEY(bId) REFERENCES bs(rowid) )

CREATE INDEX nri ON ltc(nr)

Вот урезанная версия моей команды select:

SELECT  l0.nr,l1.nr,l2.nr
FROM ltc as l0, ltc as l1, ltc as l2
WHERE 
    l0.lemId IN (1001) -- in reality 1001 is some simple sub select.
AND l1.lemId IN (1002,1003)
AND l2.lemId IN (1004 )
ORDER BY
    l0.nr,
    l1.nr,
    l2.nr
LIMIT 10;

ПЛАН EXPLAIN QUERY дает:

(0, 0, 0, u'SCAN TABLE ltc AS l0')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 1')
(1, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 1, 1, u'SCAN TABLE ltc AS l1')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 2')
(2, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 2, 2, u'SCAN TABLE ltc AS l2')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 3')
(3, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 0, 0, u'USE TEMP B-TREE FOR ORDER BY')

и это при удаленном ORDER BY (или уменьшенном только до одного столбцаorder by l0.nr):

(0, 0, 0, u'SCAN TABLE ltc AS l0 USING COVERING INDEX sqlite_autoindex_ltc_1')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 1')
(1, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 1, 1, u'SCAN TABLE ltc AS l1 USING COVERING INDEX sqlite_autoindex_ltc_1')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 2')
(2, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 2, 2, u'SCAN TABLE ltc AS l2 USING COVERING INDEX sqlite_autoindex_ltc_1')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 3')
(3, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')

Я перепробовал все виды отдельных и комбинированных индексов, но, похоже, это не имеет значения.

Проблема, похоже, заключается в самом двойном упорядочении, а не в двойном выборе: даже бесполезный двойной ORDER BY создает временное b-дерево (даже если в этом случае результат будет немедленным):

EXPLAIN QUERY PLAN SELECT  ltc.nr
FROM ltc
WHERE 
ltc.lemId = 716 
ORDER BY
    ltc.nr,
    ltc.nr
LIMIT 10;

(0, 0, 0, u'SCAN TABLE ltc')
(0, 0, 0, u'USE TEMP B-TREE FOR ORDER BY')

ВПроблема производительности SQLite ORDER BY Говорят, что запросы не могут быть упорядочены по индексу из разных таблиц. Это проблема здесь? Есть ли способ обойти? Это специфическое ограничение для Sqlite или все системы SQL делают это?

РЕДАКТИРОВАТЬ:

После добавления индекса, как предлагает CL, проблема с производительностью остается. В качестве примера возьмем более полный запрос с четырьмя поисковыми терминами:

select  l0.nr,l1.nr,l2.nr,l3.nr
    from ltc as l0, ltc as l1, ltc as l2, ltc as l3 

    where 
        l0.lemId in (select rowid from lems where lems.lem = "catch" )
        and l1.lemId in (select rowid from lems where lems.lem = "cause" )
        and l2.lemId in (select rowid from lems where lems.lem = "score" )
        and l3.lemId in (select rowid from lems where lems.lem = "guest" )

    order by
        l0.nr asc

    LIMIT 10;

дает это объяснение:

(0, 0, 0, u'SEARCH TABLE ltc AS l0 USING INDEX lid (lemId=?)')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 1')
(1, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 1, 1, u'SEARCH TABLE ltc AS l1 USING INDEX lid (lemId=?)')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 2')
(2, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 2, 2, u'SEARCH TABLE ltc AS l2 USING INDEX lid (lemId=?)')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 3')
(3, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 3, 3, u'SEARCH TABLE ltc AS l3 USING INDEX lid (lemId=?)')
(0, 0, 0, u'EXECUTE LIST SUBQUERY 4')
(4, 0, 0, u'SEARCH TABLE lems USING COVERING INDEX lem (lem=?)')
(0, 0, 0, u'USE TEMP B-TREE FOR ORDER BY')

(больше нет полных сканов.)

но: время: 388 секунд !!!

при удаленииorder byЯ получаю точно такое же объяснение за исключением последнего временного B-дерева!

время: 0,00025 секунд !!!

Этот запрос соответствует какому-то соединению. Я также могу представить запрос как (внутреннее) соединение (с условиями). Это может быть причиной того, что время кажется экспоненциально увеличивающимся с количеством поисковых терминов: {1 поисковый запрос: 0,08 секунды, 2: 0,5, 3: 3, 4: 9, 5: 116, ...} Но как-то Я не совсем понимаю, почему база данных не может просто использовать индекс наН.Р. столбец для сортировки. В конце концов, это просто много результатов, каждый из которых содержитН.Р., которые должны быть заказаны.

Как предполагает CL, я поставил основную проблему в новом вопросе:Выбор кортежей строк из таблицы Sqlite и эффективная сортировка кортежей