Поиск по базе данных с сортировкой по релевантности
Недавно мне нужно было для одного из заказчиков сделать поиск, причём не просто вывод всех совпадений, а ещё и с сортировкой по релевантности. Многие программисты (да и я тоже раньше так делал) выведут все совпадения, а потом уже в PHP начинают сортировать по релевантности. Однако, в SQL есть уже отличная возможность поиска по базе данных с сортировкой по релевантности.
Единственное условие - это сделать поля, по которым будет идти поиск, ключами FULLTEXT. А теперь сам запрос:
SELECT *, MATCH `field` AGAINST ('$search') as relev FROM `table` ORDER BY relev DESC
В данном запросе ищутся соответствия в поле "field" ключевому запросу $search (там может быть много слов). А некое количество соответствий попадает в переменную relev (по сути, релевантность), по которой потом происходит сортировка по убыванию. И это будет происходить для каждой записи в таблице.
Всё вроде бы хорошо, однако, если Вы выполните данный запрос, то с удивлением обнаружите, что чтобы Вы не подставляли в $search, а в выборке будут абсолютно все записи. Почему? А потому, что мы не написали WHERE, поэтому даже там, где "relev=0" (то есть вообще ничего не найдено), является одним из результатом выборки. И вот изменённый SQL-запрос будет таким:
SELECT *, MATCH `field` AGAINST ('$search') as relev FROM `table` WHERE MATCH `field` AGAINST ('$search')>0 ORDER BY relev DESC
С таким запросом записей без единого совпадения в выборке уже не будет, а все те, где найдено соответствия, будут отсортированы по релевантности.
А что делать, если нам нужно сделать поиск сразу по двумя полям? Тут есть 2 варианта, если Вы создадите 1 общий FULLTEXT для двух полей, то можно написать так:
SELECT *, MATCH `field_1`, `field_2` AGAINST ('$search') as relev FROM `table` WHERE MATCH `field_1`, `field_2` AGAINST ('$search') > 0 ORDER BY relev DESC
А если общий ключ создавать не хотите, то тогда надо выполнить такой запрос (каждое из полей должно быть FULLTEXT):
SELECT *, MATCH `field_1` AGAINST ('$search') + MATCH `field_2` AGAINST ('$search') as relev FROM `table` WHERE MATCH `field_1` AGAINST ('$search') + MATCH `field_2` AGAINST ('$search') > 0 ORDER BY relev DESC
Но вся прелесть данного поиска по базе данных с сортировкой по релевантности состоит в том, что он выполняется очень быстро. Намного быстрее, чем если Вы будете орудовать строковыми функциями PHP в массивах с записями. Поэтому рекомендую использовать данный SQL-запрос при поиске в большинстве случаев, даже когда не требуется вывод по релевантности.
-
Создано 21.11.2012 07:09:47
-
Михаил Русаков
Комментарии (12):
А FULLTEXT - это что?
Ответить
Полнотекстовый индекс.
Ответить
а ГДЕ В phpmyadmin найти его, никак не найду
Ответить
http://myrusakov.ru/mysql-index.html - в 4-м пункте на картинке есть иконка в виде буквы "Т", это и есть FULLTEXT.
Ответить
Здравствуйте, Михаил. А как можно сделать поиск по релевантности сразу из двух таблиц? К примеру имеется таблица article_1 с полями name и text, а также имеется таблица article_2 тоже с полями name, category и text. Необходимо сделать поиск по всем этим полям. Подскажите пожалуйста, как это можно сделать?
Ответить
Нет проблем, указывайте поля и из второй таблицы тоже. http://myrusakov.ru/sql-select-several.html
Ответить
Спасибо за информацию
Ответить
Михаил, а как при поиске сделать выборку того участка, где встречается искомое слово, как, например, реализовано у вас?
Ответить
http://myrusakov.ru/video-search.html
Ответить
а как сделать так..чтобы можно было перейти по ссылке на страницу где эта информация находится.как это сделать? и еще вопрос. у меня выводит title и я написал чтобы выводило так же text...но у меня выводит полностью весь текст статьи..как сделать так чтобы выводило 100 символом текста а дальше шло многоточие.?? Заранее спасибо)
Ответить
Где она у Вас находится так же и определять из базы. А чтобы строку порезать есть функция substr()
Ответить
здравствуйте Михаил! посмотрел ваше видео http://myrusakov.ru/video-search.html и реализовал на своем сайте такой поиск. Но, прочитав эту стаью, предположил, что этот способ лучше или быстрее, решил изменить способ реализации поиска, но ничего не вышло. Стоило ли мне менять способ или нет? эти способы чем-то особенным отличаются?
Ответить
Для добавления комментариев надо войти в систему.
Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.