Поиск в MySQL. Релевантность своими руками
DL
9.12.2001
Продолжаю начатую в сентябре тему поиска с сортировкой по релевантности в базе MySQL.
MySQL предлагает в последних версиях базы данных использовать для полнотекстового поиска индексацию FULLTEXT и конструкцию MATCH field AGAINST. Однако не на всех серверах стоит последняя версия MySQL, и не все хостинг-провайдеры хотят обновлять софт по соображениям надежности системы.
В своё время я предполагал, что поиск с сортировкой по релевантности надо будет делать в несколько запросов, и, следовательно, лучше вовсе не браться за это. Мысли, что релевантность можно подсчитывать в самом запросе отдалённо меня посещали, но я боялся и представить такую конструкцию.
Однако же, работник одной из сайтостроительных фирм Н-ска похвастался мне системой поиска, которую они применяют на своих сайтах. Я точно не запомнил запрос, попробую так воспроизвести его:
SELECT title, date_format(material_date,'%e.%c.%y') AS date1, IF(text like '%word1 word2 word3%', 3*10, 0) + IF(text LIKE '%word1%', 9, 0) + IF(text LIKE '%word2%', 9, 0) + IF(text LIKE '%word3%', 9, 0) AS relevance FROM table WHERE text LIKE '%word1%' OR text LIKE '%word2%' OR text LIKE '%word3%' ORDER BY relevance DESC, material_date DESC
Ужасно выглядит, но работает даже на старых версиях MySQL. Попробовал сравнить скорость работы с вот таким запросом:
SELECT title, date_format(material_date,'%e.%c.%y') AS date1, MATCH text AGAINST('word1 word2 word3') AS relevance FROM table WHERE text LIKE '%word1%' OR text LIKE '%word2%' OR text LIKE '%word3%' ORDER BY relevance DESC, material_date DESC
В среднем скорость универсального запроса в два раза меньше, чем использующего новые конструкции. Что вполне логично? чем больше универсальность, тем больше ресурсоёмкость.
Попробуем построить такой запрос автоматически. Как в , отрезаем длинную строку, а так же все неправильные символы и короткие слова. Рисуем запрос.
$query = "SELECT title, date_format(material_date,'%e.%c.%y') AS date1, IF(text like '%". $good_words. "%', ". (substr_count($good_words, " ") + 1). "*10, 0) + IF(text LIKE '%". str_replace(" ", "%', 9, 0) + IF(text LIKE '%", $good_words). "%', 9, 0) AS relevance FROM table WHERE text LIKE '%". str_replace(" ", "%' OR text LIKE '%", $good_words). "%' ORDER BY relevance DESC, material_date DESC";
Не очень-то сложно. Для надёжности и защиты от флуда можно ограничить количество слов в запросе.
Некоторые дополнения к прежним публикациям.
Общее количество найденных строк в таблице. Для вывода результатов поиска, разумеется, надо пользоваться оператором LIMIT (чтобы не писать каждый раз формирование этого параметра, пользуйтесь ). Если никаких операций группировки в запросе не делается, лучше подсчитать количество строк сразу в запросе ? COUNT(*), а не через функцию php mysql_num_rows(). Можете проверить на больших таблицах. Если производятся групповые операции, делаем запрос с COUNT(DISTINCT()), но без GROUP BY.
Подсветка. Если в текстах не бывает html-тегов, жить проще.
$text = preg_replace("/word1|word2|word3/i", "<b>\\0</b>", $text);
Если в тексте теги используются, то есть три варианта а) не делать подсветку б) поскольку теги пользователь не видит (разве что очень любопытный пользователь), то можно сделать поле индекса, в котором не будет тегов а символы [^\w\x7F-\xFF\s] будут заменены на пробелы (именно эти символы вырезаются из поисковой строки в самом начале, так что поиск по ним не производится). Поиск и подсветку в таком случае сделать именно по индексу. в) делать подсветку текста из обычного поля, предварительно вырезав теги функцией [].
Полная версия поискового кода, как всегда, в списке файлов.
