Blog. Just Blog

Релевантный поиск по базе MySQL

Версия для печати Добавить в Избранное Отправить на E-Mail | Категория: Web-мастеру и не только | Автор: ManHunter
Релевантный поиск по базе MySQL
Релевантный поиск по базе MySQL

Я уже писал о возможностях поиска с учетом морфологии, а теперь обещанная статья о релевантном поиске по базе MySQL. Как разъясняют словари, релевантность - в поисковых системах - мера соответствия результатов поиска задаче поставленной в запросе. То есть чем ближе найденный результат соответствует искомому, тем выше в результатах поиска он должен находиться. Применительно к выборке из базы, в строках результата релевантность должна быть представлена неким числовым значением, по которому эта выборка должна быть отсортирована.

Начнем с теории. Если мы ищем строку из нескольких слов среди нескольких текстов, то наибольшей релевантностью обладает текст, в котором встречается вся эта строка целиком и точно в том виде, как ее задали к поиску. Затем идут тексты, где есть все слова из искомой фразы, но расположенные не по порядку. После них идут тексты, где встречаются только отдельные слова, и, чем меньше слов из фразы, тем ниже релевантность. К тому же слова из заголовка текста должны иметь поисковый вес больше, чем такие же слова из текста.

Теперь попробуем это реализовать описанный выше алгоритм на PHP и MySQL. На первом шаге надо разобрать поисковую строку на отдельные слова, при этом надо будет отбросить слова, короче трех символов. Если вы внимательно читали комментарии к статье о морфологическом поиске, то наверняка увидели там полезный совет по укорачиванию длинных слов. Его я тоже задействую.
  1. // Разобрать искомую строку $search на отдельные слова
  2. preg_match_all('/[[:alnum:]]{3,}/is',stripslashes($search),$matches);
  3. $words=array_unique($matches[0]);
  4.  
  5. $true_words=Array();
  6. if (count($words)) {
  7.     foreach($words as $word) {
  8.         // Обрабатывать только слова длиннее 3 символов
  9.         if (strlen($word)>3) {
  10.             // От слов длиннее 7 символов отрезать 2 последних буквы
  11.             if (strlen($word)>7) {
  12.                 $word=substr($word,0,(strlen($word)-2));
  13.             }
  14.             // От слов длиннее 5 символов отрезать последнюю букву
  15.             elseif (strlen($word)>5) {
  16.                 $word=substr($word,0,(strlen($word)-1));
  17.             }
  18.             $true_words[]=addcslashes(addslashes($word),'%_');
  19.         }
  20.     }    
  21. }
  22. // Список уникальных поисковых слов
  23. $true_words=array_unique($true_words);
Теперь надо распределить весовые коэффициенты между полной фразой и отдельными словами, а также между заголовком и текстом. Значимость заголовка и текста я установил как пропорцию 80/20, мне кажется это наиболее оптимальное соотношение. Для полной фразы в заголовке и тексте я выбрал соотношение 60/20, а для отдельных слов 10/10. Последние коэффициенты 10 и 10 будут еще дополнительно распределяться на каждое входящее слово. Пока не очень понятно, для чего это все сделано, поэтому поясню на примере. Предположим, у нас есть текст:

Заголовок: Мама мыла раму или история успеха

Текст: Однажды мама мыла раму и заработала на этом кучу денег. Теперь у мамы своя фирма по мытью окон.

Как я уже говорил, максимальное значение релевантности будет у текстов, которые содержат искомую фразу целиком, поэтому возьмем фразу "мама мыла раму". Считаем релевантность текста:

Заголовок:
"мама мыла раму" = 60%
вес каждого отдельного слова = (20/3) = ~6.66
"мама" = 6.66%
"мыла" = 6.66%
"раму" = 6.66%

Текст:
"мама мыла раму" = 10%
вес каждого отдельного слова = (10/3) = ~3.33
"мама" = 3.33%
"мыла" = 3.33%
"раму" = 3.33%

Релевантность: ~100%

Небольшая погрешность возникла из-за округления результатов деления. Изменим заголовок текста:

Заголовок: Деловая мама или история успеха

Текст: Однажды мама мыла раму и заработала на этом кучу денег. Теперь у мамы своя фирма по мытью окон.

Смотрим как изменится релевантность при той же поисковой строке:

Заголовок:
вес каждого отдельного слова = (20/3) = ~6.66
"мама" = 6.66%

Текст:
"мама мыла раму" = 10%
вес каждого отдельного слова = (10/3) = ~3.33
"мама" = 3.33%
"мыла" = 3.33%
"раму" = 3.33%

Релевантность: ~26.7%

При этом запрос "история успеха" на том же тексте даст следующий результат:

Заголовок:
"история успеха" = 60%
вес каждого отдельного слова = (20/2) = 10
"история" = 10%
"успеха" = 10%

Текст:
ничего не найдено = 0%

Релевантность: 80%

Надеюсь, что теперь вам понятно для чего используются и как получаются эти цифры. Остался последний шаг - все вышеизложенное надо перевести на PHP и в итоге получить запрос к базе MySQL.
  1. // Вес отдельных слов в заголовке и тексте
  2. $coeff_title=round((20/count($true_words)),2);
  3. $coeff_text=round((10/count($true_words)),2);
  4.  
  5. // Формируем запрос к базе
  6. $query  "SELECT *, ";
  7. // Условия для полного совпадения фразы в заголовке и тексте
  8. $query .= "( IF (`t_title` LIKE '%".$search."%', 60, 0)";
  9. $query .= "+ IF (`t_text` LIKE '%".$search."%', 10, 0)";
  10.  
  11. // Условия для каждого из слов
  12. foreach($true_words as $word) {
  13.     $query .= "+ IF (`t_title` LIKE '%".$word."%', ".$coeff_title.", 0)";
  14.     $query .= "+ IF (`t_text` LIKE '%".$word."%', ".$coeff_text.", 0)";
  15. }
  16. $query.=") AS `relevant` FROM `articles`";
  17.  
  18. // Условие выборки - вхождение фразы или отдельных слов в заголовок или текст
  19. $query .= " WHERE (";
  20. $query .= " `t_title` LIKE '%".$search."%' OR `t_text` LIKE '%".$search."%'";
  21. // Условия для каждого из слов
  22. foreach($true_words as $word) {
  23.     $query .= " OR `t_title` LIKE '%".$word."%'";
  24.     $query .= " OR `t_text` LIKE '%".$word."%'";
  25. }
  26. $query .= ") ORDER BY `relevant` DESC";
Так, для поисковой строки "мама мыла раму" получится такой запрос:

SELECT *,
(IF (`t_title` LIKE '%мама мыла раму%', 60, 0)
+ IF (`t_text` LIKE '%мама мыла раму%', 10, 0)
+ IF (`t_title` LIKE '%мама%', 6.67, 0)
+ IF (`t_text` LIKE '%мама%', 3.33, 0)
+ IF (`t_title` LIKE '%мыла%', 6.67, 0)
+ IF (`t_text` LIKE '%мыла%', 3.33, 0)
+ IF (`t_title` LIKE '%раму%', 6.67, 0)
+ IF (`t_text` LIKE '%раму%', 3.33, 0)) AS `relevant`
FROM `articles`
WHERE
(`t_title` LIKE '%мама мыла раму%'
OR `t_text` LIKE '%мама мыла раму%'
OR `t_title` LIKE '%мама%'
OR `t_text` LIKE '%мама%'
OR `t_title` LIKE '%мыла%'
OR `t_text` LIKE '%мыла%'
OR `t_title` LIKE '%раму%'
OR `t_text` LIKE '%раму%')
ORDER BY `relevant` DESC

а для "история успеха" вот такой:

SELECT *,
(IF (`t_title` LIKE '%история успеха%', 60, 0)
+ IF (`t_text` LIKE '%история успеха%', 10, 0)
+ IF (`t_title` LIKE '%история%', 10, 0)
+ IF (`t_text` LIKE '%история%', 5, 0)
+ IF (`t_title` LIKE '%успеха%', 10, 0)
+ IF (`t_text` LIKE '%успеха%', 5, 0)) AS `relevant`
FROM `articles`
WHERE
(`t_title` LIKE '%история успеха%'
OR `t_text` LIKE '%история успеха%'
OR `t_title` LIKE '%история%'
OR `t_text` LIKE '%история%'
OR `t_title` LIKE '%успеха%'
OR `t_text` LIKE '%успеха%')
ORDER BY `relevant` DESC

Выглядит страшновато, но такие запросы обрабатываются сравнительно быстро и их можно кэшировать. Конечно, для огромных объемов информации лучше поискать другие варианты поиска, но для небольших сайтов такое решение будет вполне уместно. Например, у меня на этом блоге используется очень похожий алгоритм поиска и скорость его работы меня полностью устраивает.

Поделиться ссылкой ВКонтакте Поделиться ссылкой на Facebook Поделиться ссылкой на LiveJournal Поделиться ссылкой в Мой Круг Добавить в Мой мир Добавить на ЛиРу (Liveinternet) Добавить в закладки Memori Добавить в закладки Google
Просмотров: 14404 | Комментариев: 13

Метки: MySQL, PHP
Внимание! Статья опубликована больше года назад, информация могла устареть!

Комментарии

Отзывы посетителей сайта о статье
ManHunter (07.06.2016 в 07:46):
Не с русскими буквми, а с юникодом.
coolfox (07.06.2016 в 00:21):
Поправь '/[[:alnum:]]{3,}/is' на '/[[:alnum:]]{3,}/isu' иначе с русскими буквами не работает
Евгений (12.09.2014 в 20:56):
Юрий, SELECT id, host FROM table GROUP BY host HAVING count(host) < 3
ManHunter (07.08.2014 в 10:52):
Какое отношение это имеет к статье?
Юрий (07.08.2014 в 10:18):
Имеется поле host, как сделать чтобы при выборке выводились только те записи в котьрых поле хост не повторяется более 2 раз?
Александр (04.12.2013 в 00:56):
То что искал для свого сайта - спасибо
Максим (31.01.2013 в 14:04):
то Квази:
полнотекстовый поиск это хорошо но, по моим сведениям, MySQL на данный момент поддерживает полнотекстовый поиск со словоформами только для латиницы. С кирилицой же, введя в поиск "Гутаперче" строки со словами "Гутаперчевый" не найдешь. Знаю есть решение данной проблемы, но, имхо, костыль! В то время как, по моему, в ПроджектSQL словоформы с кирилицей ищет в полнотекстовом поиске.

Так что для сравнительно небольших баз считаю возможным использовать Like
ManHunter (05.12.2012 в 13:16):
Все консультации и выпрямление рук только за деньги.
vladyka (05.12.2012 в 13:14):
Здравствуйте! Сформировал запрос по этому примеру, но есть проблема, и никак не пойму, почему.
Например, запрос
SELECT news_id, '' as page_id, news_subject, '' as page_title, news_extended, '' as page_content, ( IF (`news_subject` LIKE '%ПАВПВ%', 60, 0)+ IF (`news_extended` LIKE '%ПАВПВ%', 10, 0)) AS `rel` FROM `root_news` WHERE ( `news_subject` LIKE '%ПАВПВ%' OR `news_extended` LIKE '%ПАВПВ%') UNION SELECT '' as news_id, page_id, '' as news_subject, page_title, '' as news_extended, page_content,( IF (`page_title` LIKE '%ПАВПВ%', 60, 0)+ IF (`page_content` LIKE '%ПАВПВ%', 10, 0)) AS `rel` FROM `root_custom_pages` WHERE ( `page_title` LIKE '%ПАВПВ%' OR `page_content` LIKE '%ПАВПВ%') ORDER BY rel DESC LIMIT 0,20
выводит 117 результатов, хотя совпадений нет ни в одном, да и слово бессмысленное. Такое же и с некоторыми нормальными словами. Как с этим бороться?
Большинство запросов обрабатываются адекватно.
Nashev (10.01.2012 в 18:19):
Респект!
Квази (14.11.2011 в 14:47):
Спасибо за развёрнутый ответ!
ManHunter (14.11.2011 в 11:23):
Не надо так категорично заявлять. FULLTEXT и MATCH-AGAINST - это хорошо и здорово, но они работают только на таблицах MyISAM, не говоря о том, что в базе придется еще хранить эти FULLTEXT-индексы.
Дальше открываем мануал MySQL: "Релевантность вычисляется на основе количества слов в данной строке столбца, количества уникальных слов в этой строке, общего количества слов в тексте и числа документов (строк), содержащих отдельное слово." Что это значит? Это значит, что короткая фраза в огромном тексте будет иметь крохотную релевантность, хотя фактически это не так.
И еще там же в мануале: "Описанная техника подсчета лучше всего работает для больших наборов текстов (фактически она именно для этого тщательно настраивалась). Для очень малых таблиц распределение слов не отражает адекватно их смысловое значение, и данная модель иногда может выдавать некорректные результаты." Для моего блога критично, чтобы поиск был максимально точный, и описанный способ мне подходит больше всего, к тому же в этой реализации я могу самостоятельно определять вес релевантности заголовка и текста.
Ну и наконец, если искомое слово встречается более чем в 50% статей, то оно вообще будет проигнорировано. Для огромной базы это, возможно, правильное поведение, но мне опять же нужна максимальная точность.
Поэтому для каждой задачи должно быть свое решение, а не закидоны "Только ZZZ - это хорошо, а все остальное хуйня".
Квази (14.11.2011 в 10:40):
Вообще-то поиск с использованием LIKE - это не есть хорошо! Нужно использовать полнотекстовый поиск, вот это вариант.

Добавить комментарий

Заполните форму для добавления комментария
Имя*:
Текст комментария (не более 2000 символов)*:

*Все поля обязательны для заполнения.
Комментарии, содержащие рекламу, ненормативную лексику, оскорбления и т.п., а также флуд и сообщения не по теме, будут удаляться. Нарушителям может быть заблокирован доступ к сайту.
Наверх
Powered by PCL's Speckled Band Engine 0.2 RC3
© ManHunter / PCL, 2008-2018
При использовании материалов ссылка на сайт обязательна
Время генерации: 0.07 сек. / MySQL: 2 (0.0039 сек.) / Память: 4.75 Mb
Наверх