Оптимизация выражения LIKE при использовании Sqlite в iOS приложении

Недавно я столкнулся с задачей оптимизации запроса к Sqlite в моем iOS приложении.
Задача заключалась в следующем. Имелся список документов (PDF файлов), словарь (список слов), ну и связь документов и слов из словаря (вхождение слов в документ). Необходимо было реализовать поиск и вывести список документов в которых есть введенное слово.

Структура базы имела следующий вид:

CREATE TABLE document (
  id Int PRIMARY KEY NOT NULL,
  root_id Int,
  name Varchar(100),
  active Tinyint
);
CREATE INDEX IDX_documentId ON document (id);
CREATE INDEX IDX_documentName ON document (name);

CREATE TABLE dictionary (
  id Int PRIMARY KEY NOT NULL,
  word Varchar(100) NOT NULL
);
CREATE INDEX IDX_dictionaryId ON dictionary (id);
CREATE UNIQUE INDEX IDX_dictionaryWord ON dictionary (word ASC);

CREATE TABLE document_index (
  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  document_id Int NOT NULL,
  word_id Int NOT NULL,
  FOREIGN KEY(document_id) REFERENCES document(id),
  FOREIGN KEY(word_id) REFERENCES dictionary(id)
);
CREATE INDEX IDX_documentIndexId ON document_index (id);
CREATE INDEX IDX_documentIndexDocId ON document_index (document_id);
CREATE INDEX IDX_documentIndexWordId ON document_index (word_id);

Запрос для извлечения всех документов в выбранных разделах, в которых есть указанное слово:

SELECT document.id, document.name
FROM document
     INNER JOIN document_index on document_index.document_id=document.id
     INNER JOIN dictionary on dictionary.id=document_index.word_id
WHERE dictionary.word LIKE @pQuery
   AND document.active = 1
   AND document.root_id in (@pRoot1, @pRoot2, @pRoot3, @pRoot4, @pRoot5, @pRoot6, @pRoot7)
LIMIT @First, @Count

При размере dicrionary~= 400K, document ~= 1K и document_index ~= 500K записей, запрос выполнялся около 30 секунд на моем iPad 2, что было недопустимо для моего приложения.

В процессе поиска решения для ускорения выполнения запроса, я выяснил что в Sqlite3 при использовании выражения LIKE индекс не учитывается, что было совсем не хорошо, учитывая мое количество записей. Отказаться от использования LIKE и заменить его на операцию сравнения на равенство, я тоже не мог, т.к. мне нужен был поиск по подстроке. Затем я наткнулся на эту статью ^[1], в ней предлагалось заменить выражение LIKE на операции >= и < + символы яяя (zzz если в базе слова на англ.) во втором выражении.

/* Было */
dictionary.word LIKE  'абакан%'
/* Стало */
dictionary.word >= 'абакан' AND dictionary.word < 'абаканяяя'

При такой оптимизации, индекс при поиске учитывается, и находятся все слова начинающиеся с 'абакан'. Время выполнения при этом составляет всего 0,5 сек! Конечно есть и ограничения, нельзя реализовать поиск по подстроке с начала строки, т.е. найти слова начинающиеся с любых символов и заканчивающися на 'абакан', но в моем конкретном случае это было и не нужно.

Автор: AlexMac

Источник ^[2]