Недавно я столкнулся с задачей оптимизации запроса к 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 и заменить его на операцию сравнения на равенство, я тоже не мог, т.к. мне нужен был поиск по подстроке. Затем я наткнулся на эту статью, в ней предлагалось заменить выражение LIKE на операции >= и < + символы яяя (zzz если в базе слова на англ.) во втором выражении.
/* Было */
dictionary.word LIKE 'абакан%'
/* Стало */
dictionary.word >= 'абакан' AND dictionary.word < 'абаканяяя'
При такой оптимизации, индекс при поиске учитывается, и находятся все слова начинающиеся с 'абакан'. Время выполнения при этом составляет всего 0,5 сек! Конечно есть и ограничения, нельзя реализовать поиск по подстроке с начала строки, т.е. найти слова начинающиеся с любых символов и заканчивающися на 'абакан', но в моем конкретном случае это было и не нужно.
Автор: AlexMac
