Сравнение особенностей LIKE '%string%' в MySQL 3.x и MySQL 4.x

Misha v.3 04.06.2004 18:43

имеем достаточно большую БД, по которой надо делать LIKE '%string%' (именно LIKE, полнотекстовый
поиск не подходит: релевантность не нужна, зато нужно точное совпадение фразы)

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

раньше делал так:
1. SELECT COUNT(*) FROM table WHERE name LIKE '%string%' - получаю количество записей
2. вычисляю сколько будет страниц
3. определяю limit и offset на основании того, что пришло из $form
4. SELECT ... FROM table WHERE name LIKE '%string%' ORDER BY name LIMIT X,Y - собственно получаю данные на интересующей странице.
5. вывожу постраничную навигацию.

LIKE '%string%' - достаточно медленная операция. даже SELECT COUNT(*) ... LIKE '%string%' выполняется
долго, и в результате имеем 2 запроса с LIKE на страницу.
к тому-же MySQL 3.x не умеет
пользоваться индексами в подобных случаях, хотя индексы на всяких случай созданы были:

...
Следующие команды SELECT не будут использовать индексы:
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";
...

решили перейти на MySQL 4.x для которого в доке написано сделующее:

...
В версии MySQL 4.0 производится другая оптимизация на выражении LIKE.
Если используется выражение ... LIKE "%string%" и длина строки (string) больше, чем 3 символа,
то MySQL будет применять алгоритм Турбо Бойера-Мура для инициализации шаблона для строки и
затем использовать этот шаблон, чтобы выполнить поиск быстрее.
...

однако после перехода на 4.x ускорения выполнения подобных запросов обнаружено не было.

однако после изучения документации была обнаружена другая фича MySQL 4:

...
FOUND_ROWS()
Возвращает количество строк, которые возвратила бы последняя команда SELECT SQL_CALC_FOUND_ROWS ...
при отсутствии ограничения оператором LIMIT.
...

несмотря на то, что в доке есть несколько пугающий комментарий:

...
Отметим, что, хотя при использовании команды SELECT SQL_CALC_FOUND_ROWS ..., MySQL должен
пересчитать все строки в наборе результатов, этот способ все равно быстрее, чем без LIMIT,
так как не требуется посылать результат клиенту
...

я решил попробовать использовать её, для чего несколько изменил алгоритм:
1. определяем offset/limit на основании того, что пришло из $form
2. SELECT SQL_CALC_FOUND_ROWS ... FROM table WHERE name LIKE '%string%' ORDER BY name LIMIT X,Y - получаю данные
3. SELECT FOUND_ROWS() - узнаю сколько всего было бы записей если бы я не указал LIMIT
4. вычисляем сколько будет страниц
5. выводим постраничную навигацию.

в результате оказалось, что запрос в п.2 во втором случае выполняется быстрее, чем запрос в п.4 в первом
случае (почему - я не знаю, и ответа в документации не нашел), а SELECT FOUND_ROWS() выполняется мгновенно.

т.е. как минимум имеем экономию одного запроса с LIKE.

в реалиях оказалось, что если раньше максимальное время выполнения запроса с LIKE частенько превышало 30 сек,
то после изменения оно ни разу не превысило 6 сек. а в то время, когда в первом случае запрос выполнятся 30 сек
ещё добавлялось время выполнения SELECT COUNT(*) ... LIKE которое доходило до 5 сек.

правда EXPLAIN при этом показывает более страшную информацию (ALL), но я уж прямо и не знаю чему
верить: логу с реальными цифрами (одна и та-же БД) или результатам EXPLAIN :)

может кому пригодиться.

недостатки подобного подхода:
1. mysql 4 required
2. mysql specific, т.е. для переносимости на другой SQL сервер придется переделывать логику.
3. мой ?mode=debug не работал, т.к. он после каждого запроса он делал EXPLAIN, после
которого SELECT FOUND_ROWS() возвращает 0. пришлось его переделывать: складывать все запросы в
кучку и делать explain после всех живых запросов прямо перед выдачей информации.
переделаный класс пока не выложил, тестируется...

---