ИНДЕКС SQL не используется для условия WHERE ABS(x-y) ‹ k, но используется для условия y - k ‹ x ‹ y + k

У меня есть запрос, включающий пары строк с разницей во времени менее 2 часов (~0,08333 дня) :

SELECT mt1.*, mt2.* FROM mytable mt1, mytable mt2 
                    WHERE ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 0.08333

Этот запрос довольно медленный, т.е. ~ 1 секунда (в таблице ~ 10k строк).

Идея заключалась в том, чтобы использовать INDEX. Очевидно, что CREATE INDEX id1 ON mytable(date) ничего не улучшил, это нормально.

Затем я заметил, что магический запрос CREATE INDEX id2 ON mytable(JULIANDAY(date))

  1. не помогло при использовании:

    ... WHERE ABS(JULIANDAY(mt1.date) - JULIANDAY(mt2.date)) < 0.08333

  2. не помогло при использовании:

    ... WHERE JULIANDAY(mt2.date) - 0.08333 < JULIANDAY(mt1.date) < JULIANDAY(mt2.date) + 0.08333

  3. ... но значительно улучшил производительность (время запроса счастливо разделено на 50!) при использовании:

    ... WHERE JULIANDAY(mt1.date) < JULIANDAY(mt2.date) + 0.08333
      AND JULIANDAY(mt1.date) > JULIANDAY(mt2.date) - 0.08333

Конечно, 1., 2. и 3. эквивалентны, поскольку математически

|x-y| < 0.08333 <=> y - 0.08333 < x < y + 0.08333
                <=> x < y + 0.08333 AND x > y - 0.08333

Вопрос. Почему решения 1 и 2 не используют ИНДЕКС, а решение 3 использует его?

Примечание:

  • Я использую модуль Python + Sqlite sqlite3

  • Тот факт, что решения 1. и 2. не используют индекс, подтверждается при выполнении EXPLAIN QUERY PLAN SELECT ...:

    (0, 0, 0, u'SCAN TABLE mytable AS mt1')
(0, 1, 1, u'SCAN TABLE mytable AS mt2')

    Решение факта 3. использует индекс, показанный при выполнении EXPLAIN QUERY PLAN SELECT ...:

    (0, 0, 1, u'SCAN TABLE mytable AS mt2')
(0, 1, 0, u'SEARCH TABLE mytable AS mt1 USING INDEX id2 (<expr>>? AND <expr><?)')

sql sqlite indexing explain
person Basj    schedule 17.04.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Я считаю, что включение AND является аргументом в соответствии с:

Предложение WHERE в запросе разбивается на «термы», где каждый термин отделяется от других оператором И. Если предложение WHERE состоит из ограничений, разделенных оператором ИЛИ, то все предложение считается одним «термом», к которому применяется оптимизация предложения ИЛИ.

Обзор оптимизатора запросов SQLite

Возможно, стоит запустить ANALYZE, чтобы посмотреть, улучшит ли это ситуацию.

Согласно комментарию:

Я думаю, ранее добавленный абзац может прояснить, почему ABS(x-y) ‹ k не использует индекс и почему x ‹ y + k использует его, вы так не думаете? Вы хотите включить этот абзац? [Все термины предложения WHERE анализируются, чтобы увидеть, могут ли они быть удовлетворены с помощью индексов. Для использования в индексе термин должен иметь одну из следующих форм: столбец = выражение, столбец IS выражение, столбец > выражение...

Добавлено следующее.

Для использования в индексе термин должен иметь одну из следующих форм:
столбец = выражение
столбец IS выражение
столбец > выражение
столбец >= выражение
столбец ‹ выражение
столбец ‹= выражение
выражение = столбец
выражение > столбец
выражение >= столбец
выражение ‹ столбец
выражение ‹= столбец
столбец IN (список-выражений)
столбец IN (подзапрос)
столбец IS NULL

Я не уверен, что это будет работать с BETWEEN (например, WHERE column BETWEEN expr1 AND expr2).

person MikeT    schedule 17.04.2018
comment
Спасибо за ваше редактирование. Я думаю, вы можете удалить As per the comment: I think the previously added ... абзац -› это было просто в режиме комментариев/обсуждений/чатов и не имело отношения к форме технического обсуждения/ответа. Подходящей аккредитацией будет sqlite.org/optoverview.html#where_clause_analysis. - person Basj; 18.04.2018

arrow_upward
1
arrow_downward

Вы используете индекс выражения. В документации говорится:

Планировщик запросов SQLite рассмотрит возможность использования индекса для выражения, когда индексируемое выражение появляется в предложении WHERE или в предложении ORDER BY запроса, в точности, как это написано в операторе CREATE INDEX. . Планировщик запросов не занимается алгеброй.

Таким образом, невозможно использовать индекс для ускорения поиска вызова abs(), если индексированное выражение является только параметром. (И невозможно проиндексировать весь вызов abs(), потому что он включает две таблицы.)

Таким образом, преобразование выражения, как вы это сделали, — единственный способ сделать его более эффективным.

(Обратите внимание, что a<b<c сначала сравнивает a и b, а затем сравнивает полученное логическое значение с c. Это не то, что вам нужно.)

person CL.    schedule 18.04.2018