Оптимизация в SQLite

Question

[Juggler]

Есть необходимость ускорения поиска в БД SQLite в одном проекте на iPhone.

База большая, более миллиона записей. Есть несколько текстовых полей varchar, по которым ведется поиск. Информация на русском языке UTF8.

В настоящий момент используется LIKE + пользовательская функция перевода в нижний регистр. Проблема в том, что в этом случае нельзя использовать индекс. В итоге запрос выполняется почти минуту.

Что можете посоветовать для ускорения?

Answer 1

[Krovosos]

Если поиск ведется по условию 'XXX%', то индекс использовать можно.

Если нужен полнотекстовый поиск, то смотреть здесь

Comments

[Juggler]

FTS3/FTS4 — это хороший выход, обязательно проверю. Спасибо.
LIKE 'XXX%' индекс не подхватывает — я проверил с помощью EXPLAIN QUERY PLAN…
Подхватывает только с вариантом > 'aaa' AND < 'aab'

Answer 2

[Krovosos]

LIKE не подхватывает, потому что есть тонкости. Про них можно почитать здесь.

Вкратце:
1) столбец в таблице обязан иметь тип TEXT:

foo(bar TEXT) — хорошо, foo (bar) — плохо

2) по умолчанию: LIKE регистронезависим, а COLLATION (т.е. способ сравнения данных) используется BINARY, то есть обычное посимвольное совпадение = регистрозависим.

Поэтому нужно либо включить режим case-sensitive для LIKE командой

PRAGMA case_sensitive_like = 1;

Либо в индексе указать нужный тип COLLATION:

CREATE INDEX idx ON foo (name COLLATE NOCASE);

3) LIKE должен быть записан как <СТОЛБЕЦ> LIKE «XXX%»

Т.е. слева — столбец, справа — строковый литерал, который _не начинается_ на wildcard.

Проверил вариант с case_sensitive_like = 1; индекс используется.

Comments

[Juggler]

Спасибо. Похоже, для этого варианта нужно делать отдельную таблицу с транслитом текста. Ограничение в том, что произвольные символы будут только справа.
Попробую FTS, возможно в моем случае будет эффективнее. Там можно сделать виртуальные таблицы уже в транстите и искать по ним с учетом индекса по MATCH, что позволит использовать аналоги wildcards с обеих сторон.

[Krovosos]

В FTS основная проблема — это написать токенизатор, который извлекает лексемы. Токенизатор по умолчанию будет считать разными слова, которые отличаются по написанию (т.е. нет учета склонений).

[Juggler]

Для мобильного приложения такие навороты избыточны. А в общем, это называется stemmer, гугление дает ощутимое количество вариантов алгоритма.

Answer 3

[FloppyFormator]

Самый хороший способ — это заставить SQLite работать с русским языком по-человечески. Т. к. отсутствие поддержки ещё выползет боком в разных других местах. Но если это невозможно, можно продублировать все поля, по которым ведётся поиск, и хранить в них текст в нижнем регистре. Ну и программно следить за тем, чтобы значения в них соответствовали исходным полям.

Comments

[Juggler]

Речь об оптимизации. Таблица без индексов занимает 120MB. Можно сделать отдельное поле (даже в отдельной таблице) — в нижнем регистре, в транслите — для удобной индексации. Размер БД удвоится — новая таблица+индекс. Как искать по такому полю без LIKE, чтобы использовать индекс?