Как заставить Sqlite использовать индекс для упорядочения по нескольким столбцам в случае множественного выбора из одной таблицы?

У меня есть таблица с несколькими сотнями тысяч строк. (Это предварительно вычисленная таблица, выражающая связь между леммами слов и другими большими таблицами.) Мне нужно сделать несколько выборок, чтобы найти комбинацию разных записей, т.е. мне нужно использовать "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, ...} Но каким-то образом, Я не совсем понимаю, почему база данных не может просто использовать индекс для столбца nr для сортировки. В конце концов, это просто много результатов, каждый из которых содержит nr, которые должны быть упорядочены.

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

Источник

1 ответ

Решение

Индекс можно использовать для ускорения сортировки, только если запрос позволяет вернуть строки в том порядке, в котором они хранятся в индексе.

Это невозможно, если для поиска строк используется другой столбец с другим индексом или когда вы возвращаете несколько строк из-за перекрестного соединения.

Попробуйте добавить индекс на lemId, но это вряд ли поможет с сортировкой.

Сортировка медленная, потому что перед LIMIT слишком много результатов.

Источник
Другие вопросы по тегам