Задача
Вы хотите найти при помощи индекса FULLTEXT фразу, то есть набор смежных слов, расположенных в определенном порядке.
Решение
Используйте возможность поиска фразы, предоставляемую FULLTEXT поиском, или комбинируйте FULLTEXT поиск слов и обычный поиск по образцу.
Обсуждение
Чтобы найти записи, содержащие определенную фразу, недостаточно просто выполнить FULLTEXT поиск:
mysql> SELECT COUNT(*) FROM kjv
> WHERE MATCH(vtext) AGAINST('still small voice');
+------------+
| COUNT(*) |
+------------+
| 548 |
+------------+
Запрос возвращает результат, но не тот, который хотелось бы получить. FULLTEXT поиск вычисляет релевантность по присутствию каждого отдельного слова, вне зависимости от того, где именно в столбце vtext оно встретилось. Величина релевантности будет ненулевой до тех пор, пока поиск будет обнаруживать хотя бы одно слово. Поэтому такие запросы обычно находят слишком много записей.
В MySQL версии 4.0.2 у FULLTEXT поиска появилась возможность поиска фраз в логическом режиме. Если вы хотите найти строки, содержащие какуюто фразу, просто заключите ее в двойные кавычки:
mysql> SELECT COUNT(*) FROM kjv
> WHERE MATCH(vtext) AGAINST('"still small voice"' IN BOOLEAN MODE);
+------------+
| COUNT(*) |
+------------+
| 1 |
+------------+
Если же вы используете более раннюю версию, необходим обходной путь. Можно выполнить поиск в логическом режиме, потребовав присутствия каждого слова, но проблема все же не будет решена, так как порядок слов никак не учитывается:
mysql> SELECT COUNT(*) FROM kjv
> WHERE MATCH(vtext)
> AGAINST('+still +small +voice' IN BOOLEAN MODE);
+------------+
| COUNT(*) |
+------------+
| 3 |
+------------+
Если же использовать поиск по шаблону SQL, то будет возвращен правильный результат:
mysql> SELECT COUNT(*) FROM kjv
> WHERE vtext LIKE '%still small voice%';
+------------+
| COUNT(*) |
+------------+
| 1 |
+------------+
Однако поиск по шаблону SQL обычно работает медленнее, чем FULLTEXT поиск. Похоже, вы оказались перед неприятным выбором: использовать быстрый способ, не выводящий желаемых результатов, или же корректно работающий, но медленный способ. К счастью, есть еще вариант: вы можете объединить оба способа в одном запросе:
mysql> SELECT COUNT(*) FROM kjv
> WHERE MATCH(vtext) AGAINST('still small voice')
> AND vtext LIKE '%still small voice%';
+------------+
| COUNT(*) |
+------------+
| 1 |
+------------+
Берем лучшее из каждого способа:
• С помощью выражения MATCH() MySQL может выполнить FULLTEXT поиск для формирования множества строккандидатов, содержащих слова из фразы. Тем самым значительно сужается круг поиска.
• Используя сравнение с шаблоном SQL, MySQL просматривает строки кандидаты для вывода тех строк, в которых слова расположены в нужном порядке.
Данный прием не сработает, если все слова короче минимума, указанного для индексирования, или если слова встречаются более чем в половине записей. В подобных случаях FULLTEXT поиск не вернет ни одной строки, но вы все еще можете выполнить поиск по шаблону SQL.