SQLite: должен ли LIKE 'searchstr%' использовать индекс?

У меня есть БД с несколькими полями

word_id — INTEGER PRIMARY_KEY
word — TEXT
...

..и ~150 тыс. строк.

Поскольку это словарь, я ищу слово с маской 'search_string%', используя LIKE. Раньше он работал нормально, на поиск совпадающих строк уходило 15 мс. В таблице есть индекс для поля 'word'. Недавно я изменил таблицу (некоторые поля этой таблицы, которые выходят за рамки), и что-то случилось — выполнение запроса занимает 400 мс, я так понимаю, что сейчас он не использует индекс. Прямой запрос с = вместо like показывает результат 10 мс. У кого-нибудь есть идея, что здесь происходит?


cocoa sql sqlite sql-like query-performance
person nikans    schedule 21.12.2011    source источник
comment
я так думаю, вы, вероятно, захотите взглянуть на b-tree, поскольку b-tree эффективны в запросе диапазона и подобном сравнении.   -  person Jasonw    schedule 21.12.2011
comment
Хм, а индексация не предполагает создание b-tree? Вы имеете в виду, что я должен создать B-дерево самостоятельно?   -  person nikans    schedule 21.12.2011
comment
% всегда только в конце вашего поискового запроса, а не в начале?   -  person Michael Low    schedule 21.12.2011
comment
Да, всегда в начале.   -  person nikans    schedule 21.12.2011
comment
Может попробовать сбросить и заново создать индекс?   -  person ᆼᆺᆼ    schedule 21.12.2011
comment
есть несколько типов индексов, которые можно указать при создании индекса. например, в mysql есть b-дерево, хэш и r-дерево. но поскольку вы используете sqlite, я не знаком с ним, поэтому эксперт sqlite может прокомментировать дальше.   -  person Jasonw    schedule 21.12.2011
comment
Спасибо. Я уже пробовал пересоздавать индекс, делать несколько индексов для нескольких полей и так далее. Я помню, что несколько месяцев назад, когда я создавал эти индексы (которые работали), я использовал необычный запрос, например CREATE INDEX idxname ON table(words) __SOMETHING_HERE__, и я не могу вспомнить, что было в конце запроса. Может быть, мне это приснилось, потому что это маловероятно ))   -  person nikans    schedule 21.12.2011
comment
Вы должны попробовать использовать EXPLAIN QUERY PLAN, чтобы увидеть, что происходит с вашим запросом.   -  person Evgeny Shurakov    schedule 21.12.2011
comment
Спасибо, Евгений :) спасибо, думал об этом, но не пробовал.   -  person nikans    schedule 21.12.2011
comment
Кстати, как бы я сделал это на iPhone? Я имею в виду, просто введите EXPLAIN QUERY PLAN SELECT ... и посмотрите, что происходит в консоли или как?   -  person nikans    schedule 21.12.2011
comment
Хорошим кандидатом для кластеризованного индекса будет (word, word_id), если это возможно в SQLite.   -  person onedaywhen    schedule 21.12.2011
comment
да, это возможно. Я попробую, если это сделает запрос быстрее, спасибо.   -  person nikans    schedule 21.12.2011
comment
нет, кажется, что с кластерным индексом он в 10 раз медленнее   -  person nikans    schedule 23.12.2011

Ответы (1)


arrow_upward
35
arrow_downward

В этом случае нельзя безопасно использовать индекс. Наивная реализация изменила бы это:

... WHERE word LIKE 'search_string%'

в

... WHERE word >= 'search_string' AND word < 'search_strinh'

путем увеличения последнего символа строки поиска. Операторы «больше» и «меньше» могут использовать индекс, а LIKE — нет.

К сожалению, в общем случае это не сработает. Оператор LIKE нечувствителен к регистру, что означает, что 'a' LIKE 'A' истинно. Приведенное выше преобразование нарушит любую строку поиска с заглавными буквами.

Однако в некоторых случаях вы знаете, что чувствительность к регистру не имеет значения для конкретного столбца, и описанное выше преобразование безопасно. В этом случае у вас есть два варианта.

  1. Используйте последовательность сопоставления NOCASE для индекса, который охватывает это конкретное поле.
  2. Измените поведение оператора LIKE во всей программе, запустив PRAGMA case_sensitive_like = ON;

Любое из этих действий позволит SQLite прозрачно выполнить описанное выше преобразование за вас; вы просто продолжаете использовать LIKE как всегда, и SQLite перепишет базовый запрос, чтобы использовать индекс.

Подробнее об оптимизации LIKE можно прочитать на странице обзора оптимизатора запросов SQLite.

person BJ Homer    schedule 21.12.2011
comment
Проклятие! В теме выше я упомянул, что использовал некоторые параметры при создании индексов (которые работали) в прошлый раз. Итак, это был COLLATE NOCASE. Я потратил около 6 часов безрезультатно, выясняя это. Хорошая ссылка, я тоже ее читал, но, похоже, просто ускользнул от моего внимания. Спасибо, Гомер! Вы только что сэкономили мне бог знает сколько времени. - person nikans; 21.12.2011
comment
Я думаю, вы имеете в виду PRAGMA case_sensitive_like = ON; Как вы сказали, LIKE по умолчанию нечувствителен к регистру. Из статьи, на которую вы ссылаетесь: оптимизация LIKE может произойти, если столбец, указанный слева от оператора, индексируется с использованием встроенной последовательности сортировки BINARY и включен case_sensitive_like. - person Roman Levin; 11.12.2014
comment
Ах, правда. Я исправлю это. Спасибо! - person BJ Homer; 11.12.2014