Выбор кортежей строк из таблицы Sqlite и эффективная сортировка кортежей
У меня очень большая таблица с четырьмя столбцами
+-----+-------+-----+-----+ | номер | лемид | CID | бид | +-----+-------+-----+-----+ | ... | | | | | 611 | 19 | 2 | 3 | | 611 | 19 | 3 | 3 | | 611 | 19 | 4 | 3 | | 611 | 19 | 5 | 3 | | 611 | 19 | 6 | 3 | | 611 | 19 | 1 | 3 | | 612 | 19 | 18 | 3 | | 612 | 19 | 7 | 3 | | 612 | 19 | 8 | 3 | | 613 | 19 | 1 | 205 | | 613 | 19 | 18 | 205 | | 614 | 19 | 2 | 224 | | 615 | 19 | 2 | 249 | | ... | | | | | 659 | 20 | 14 | 1434| | 659 | 20 | 15 | 1434| | 659 | 20 | 16 | 1434| | 659 | 20 | 17 | 1434| | 660 | 20 | 14 | 1483| | 660 | 20 | 15 | 1483| | 648 | 20 | 1 | 205 | | 648 | 20 | 18 | 205 | | 649 | 20 | 2 | 249 | | 649 | 20 | 3 | 249 | | 649 | 20 | 8 | 249 | | 650 | 20 | 4 | 279 | | 650 | 20 | 5 | 279 | | ... | | | | +-----+-------+-----+-----+
Для n lemIds (lem0, lem1,...) я хочу выбрать кортеж из n разных строк в таблице со следующими свойствами:
- row0: lemId = lem0,
- строка1: lemId = lem1,
- и т.п.
- все n строк должны иметь одинаковый идентификатор
- все n строк должны иметь разные значения
Это можно сделать с помощью множественного выбора. Вот пример для двух лемидов (19 и 20)
SELECT l0.cId,l0.bId,l1.bId
FROM ltc AS l0, ltc AS l1
WHERE
l0.cId=l1.cId AND l0.bId!=l1.bId
AND l0.lemId = 19
AND l1.lemId = 20
LIMIT 10 OFFSET 0;
До здесь все идет хорошо и быстро.
Мне нужно получить строки результатов в порядке, который смешивает CID, то есть, если в результатах, например, 20 различных CID, мне нужны эти разные кортежи результатов, прежде чем один и тот же CId повторяется. Иными словами, если в скажем 1000 наборов результатов (например, cId, bId0, bId1) есть, скажем, 20 различных cIds (скажем, от 1 до 20), мне нужно получить результаты следующим образом:
(1, …)
(2, …)
…
(20, …)
(1, …)
(2, …)
...
По этой причине я предварительно вычислил значение при вставке информации: nr. При сортировке по этому значению он дает мне желаемый порядок:
order by
l0.nr asc,
l1.nr asc
Проблема в том, что эта сортировка чрезвычайно медленная, и кажется невозможным использовать какой-либо вид индексации, как предложено в ответе Как заставить Sqlite использовать индекс для упорядочения по нескольким столбцам в случае множественного выбора из одной таблицы? по крайней мере, используя этот способ запроса. Более того, кажется, что время запроса растет экспоненциально с размером n кортежа, возможно, из-за построения временного B-дерева в процессе сортировки.
Есть ли какой-то способ получить результат эффективно, возможно, даже без использования nr?
Вот некоторые несортированные результаты из вышеприведенного запроса:
+ ----- + ----- + ----- + | CID | b0 | б1 | +-----+-----+-----+ | 1 | 3 | 205 | | 2 | 3 | 249 | | 3 | 3 | 249 | | 4 | 3 | 279 | | 4 | 3 | 321 | | 5 | 3 | 279 | | 5 | 3 | 321 | | 6 | 3 | 321 | | 6 | 3 | 386 | | 7 | 3 | 321 | | 7 | 3 | 386 | | 8 | 3 | 249 | | 18 | 3 | 205 | | 1 | 3 | 205 | | 2 | 3 | 249 | | 3 | 3 | 249 | | 4 | 3 | 279 | | 4 | 3 | 321 | | 5 | 3 | 279 | | 5 | 3 | 321 | | 6 | 3 | 321 | | 6 | 3 | 386 | | 7 | 3 | 321 | | 7 | 3 | 386 | | 8 | 3 | 249 | | 18 | 3 | 205 | | 1 | 205 | 3 | | 1 | 205 | 3 | | 18 | 205 | 3 | | 18 | 205 | 3 | | 2 | 224 | 3 | | 2 | 224 | 3 | | 2 | 224 | 249 | | 2 | 249 | 3 | | 2 | 249 | 3 | | 3 | 249 | 3 | | 3 | 249 | 3 | | 8 | 249 | 3 | | 8 | 249 | 3 | | 4 | 279 | 3 | | 4 | 279 | 3 | | 4 | 279 | 321 | | 5 | 279 | 3 | | 5 | 279 | 3 | | 5 | 279 | 321 | | 4 | 321 | 3 | | 4 | 321 | 3 | | 4 | 321 | 279 | | 5 | 321 | 3 | +-----+-----+-----+
Ответ CL правильный, и мне удалось переделать мою базу данных, чтобы найти вокалы (базовые формы, разделяемые разными леммами) и таким образом использовать то, что предложил CL. Это позволило мне избежать:
WHERE ltc2.lemId in (21, 22)
и вместо
WHERE ltc2.vocabId = 11
Я закончил тем, что сначала посмотрел на vocabId с помощью отдельного запроса (в Python!!!), а затем выполнил сложный запрос, предложенный CL. При перемещении этот запрос увеличивается примерно на дюжину строк с каждым дополнительным термином запроса. Но все же: так стало очень быстро.
Итак, есть один дополнительный вопрос, если я могу: тот факт, что даже один
WHERE ltc2.lemId in (21)
намного медленнее, чем
WHERE ltc2.lemId = 21
меня удивляет: это баг или фича?
Точнее, ожидаете ли вы того же падения производительности с любой системой баз данных, или это что-то особенное для Sqlite?
1 ответ
Следующий запрос возвращает одну комбинацию bIdс за cId:
SELECT cId,
bId AS bId19,
(SELECT min(bId)
FROM ltc AS ltc1
WHERE ltc1.lemId = 20
AND ltc1.cId = ltc0.cId
AND ltc1.bId != ltc0.bId
) AS bId20
FROM ltc AS ltc0
WHERE lemId = 19
AND bId20 IS NOT NULL
ORDER BY bId19, cId
Использование подзапроса вместо объединения - это самый простой способ обеспечить вычисление только одной комбинации.
Невозможно сослаться на выражение в предложении SELECT из другого выражения в том же предложении SELECT, поэтому, если у вас есть три или более bIds, вам нужно ввести больше подзапросов:
SELECT cId,
bId19,
bId20,
(SELECT min(bId)
FROM ltc AS ltc2
WHERE ltc2.lemId = 21
AND ltc2.cId = ltc0.cId
AND ltc2.bId != ltc0.bId19 -- and/or bId20?
) AS bId21
FROM (SELECT cId,
bId AS bId19,
(SELECT min(bId)
FROM ltc AS ltc1
WHERE ltc1.lemId = 20
AND ltc1.cId = ltc0.cId
AND ltc1.bId != ltc0.bId
) AS bId20
FROM ltc AS ltc0
WHERE lemId = 19) AS ltc0
WHERE bId20 IS NOT NULL
AND bId21 IS NOT NULL
ORDER BY bId19, cId
С индексом на (lemId, bId, cId)сортировка не требуется. Индекс на (lemId, cId, bId) ускорит поиск подзапроса, но не очень.
Что касается возврата всех комбинаций: я не вижу механизма для реализации "смешивания", которое могло бы быть более эффективным, чем предварительно вычисленные nr,
Любой запрос должен искать строки, выполняя поиск на lemId Во-первых, чтобы иметь возможность использовать индекс для сортировки, вам нужен индекс на (lemId, nr), Однако это позволяет ускорить сортировку только по одному nr столбец, и только с поддержкой частичной сортировки в SQLite 3.8.5 или более поздней версии, так что это может быть или не быть достаточно быстрым для вас.
(Просто используйте ваш оригинальный запрос с несколькими объединениями.)
Когда поиск на ltc0.lemId использует несколько идентификаторов (lemId IN (1,2)) или подзапрос (lemId IN (SELECT ...)), сортировка индекса по nr не возможно вообще. Из-за большого количества возвращаемых строк сортировка будет медленной.