Псевдорелевантный SQL-поиск
- Опубликовано: 05.04.2006
Столкнувшись с необходимостью реализовывать для посетителей своего сайта более-менее поиск по базе данных, используемой в проектах «Кино» и «Книги», и решив эту проблему вполне приемлемым способом, я решил поделиться опытом с другими SQL-разработчиками.
Сразу предупрежу, что здесь вы не найдёте серьёзной теории по механизмам поиска и тем более открытия секретов функционирования таких «поисковых монстров», как «Яндекс» или Google. Скорее, я просто решал частную техническую задачу, хотя в порядке повышения общей эрудиции с кое-какой скудной теорией по этому вопросу и познакомился. Некоторые полезные ссылки для особо интересующихся:
- MySQL 5.0 Full-Text Search Functions (англ.)
- Вопрос “SQL in Need of Optimization” на одном из форумов (англ.)
- Вопрос «Типы данных и поиск» на форуме MySQL.ru
- Категориальное упорядочение результатов запроса
- Набросок алгоритма поиска по частям с учётом индекса релевантности на форуме SQL.ru
Но вернёмся к данной статье. Начну с объяснения её названия. Прежде всего – что такое релевантность? Релевантность (от англ. “relevance” – уместность, соответствие, адекватность) – это мера соответствия результатов поискового запроса сути самого запроса. Я назвал объясняемый здесь механизм «псевдорелевантным» потому, что хоть он и учитывает такую меру соответствия и ранжирует результаты по ней, но на самом деле делается это без серьёзной теоретической проработки вопроса и без особо изощрённого алгоритма расчёта индекса релевантности. Ну а «SQL-поиск» это потому, что в его реализации будет использован только самый обычный SQL (скажем, соответствующий стандарту SQL-92).
Предположим, что у нас есть некоторая таблица MyTable с неким строковым полем MyField, по которому нужно осуществить поиск. И пусть она условно содержит следующие записи:
# | MyField |
1 | fulL fooL |
2 | Full Fool |
3 | relevancE iS fulL |
4 | Relevance Is Full |
5 | morE fulL relevancE arounD |
6 | More Full Relevance Around |
7 | fulL relevancE |
8 | Full Relevance |
Определим уровни релевантности, то есть степени соответствия результатов запросу. Изначально я «придумал» 4 таких уровня, но вспомнив о возможной регистрозавимости поиска, всё-таки насчитал их 8 штук. Ниже они приводятся в порядке уменьшения релевантности.
- Значение поля в точности равно искомой строке, с учётом регистра символов.
- Значение поля равно искомой строке, но без учёта регистра символов.
- Значение поля содержит искомую строку в качестве подстроки, с учётом регистра символов.
- Значение поля содержит искомую строку в качестве подстроки, без учёта регистра символов.
- Значение поля содержит все слова искомой строки в любом порядке, с учётом регистра символов.
- Значение поля содержит все слова искомой строки в любом порядке, без учёта регистра символов.
- Значение поля содержит любые слова (одно или несколько) искомой строки, с учётом регистра символов.
- Значение поля содержит любые слова (одно или несколько) искомой строки, без учёта регистра символов.
То есть, если мы будем искать строку “Full Relevance” в приведённом выше примере таблицы, то сначала будет выведена запись № 8 (так как значение поля MyField этой записи в точности равно искомой строке с учётом регистра), потом – № 7 (равно без учёта регистра), № 6 (содержит как подстроку с учётом регистра), № 5 (как подстроку без учёта регистра) и т. д. – в общем, в обратном порядке.
Каждый из уровней релевантности выбирается отдельным SQL-запросом (например, для п. 1 это SELECT * FROM MyTable WHERE BINARY MyField = 'Full Relevance'), вместе же они объёдиняются посредством SQL-оператора UNION. И вот что получается (на том же примере):
SELECT * FROM MyTable WHERE BINARY MyField = 'Full Relevance' UNION SELECT * FROM MyTable WHERE MyField = 'Full Relevance' UNION SELECT * FROM MyTable WHERE BINARY MyField LIKE '%Full Relevance%' UNION SELECT * FROM MyTable WHERE MyField LIKE '%Full Relevance%' UNION SELECT * FROM MyTable WHERE (BINARY MyField LIKE '%Full%') AND (BINARY MyField LIKE '%Relevance%') UNION SELECT * FROM MyTable WHERE (MyField LIKE '%Full%') AND (MyField LIKE '%Relevance%') UNION SELECT * FROM MyTable WHERE (BINARY MyField LIKE '%Full%') OR (BINARY MyField LIKE '%Relevance%') UNION SELECT * FROM MyTable WHERE (MyField LIKE '%Full%') OR (MyField LIKE '%Relevance%')
Конечно, тут же возникает вопрос о правильности определения вхождения слова в значение поля как %слово% (ведь при этом будут найдены не только и не столько искомые слова целиком, но и части других слов). Однако практика показывает, что подобный упрощённый SQL-запрос даёт вполне приемлемые результаты. Понятное дело, что запрос должен формироваться приложением динамически, с учётом разбора ввода пользователя по словам.
Если есть необходимость искать не по одному полю, а по нескольким сразу, в том числе по полям другой таблицы, то определённые выше уровни релевантности продолжают действовать, а вот их техническая реализация в виде SQL-запроса дополняется соответствующими сравнениями по другим полям, между собой объединяющимися оператором OR:
SELECT * FROM MyTable WHERE (BINARY MyField = 'Full Relevance') OR (BINARY MyField2 = 'Full Relevance') UNION SELECT * FROM MyTable WHERE (MyField = 'Full Relevance') OR (MyField2 = 'Full Relevance') UNION SELECT * FROM MyTable WHERE (BINARY MyField LIKE '%Full Relevance%') OR (BINARY MyField2 LIKE '%Full Relevance%') UNION SELECT * FROM MyTable WHERE (MyField LIKE '%Full Relevance%') OR (MyField2 LIKE '%Full Relevance%') UNION SELECT * FROM MyTable WHERE ((BINARY MyField LIKE '%Full%') AND (BINARY MyField LIKE '%Relevance%')) OR ((BINARY MyField2 LIKE '%Full%') AND (BINARY MyField2 LIKE '%Relevance%')) UNION SELECT * FROM MyTable WHERE ((MyField LIKE '%Full%') AND (MyField LIKE '%Relevance%')) OR ((MyField2 LIKE '%Full%') AND (MyField2 LIKE '%Relevance%')) UNION SELECT * FROM MyTable WHERE ((BINARY MyField LIKE '%Full%') OR (BINARY MyField LIKE '%Relevance%')) OR ((BINARY MyField2 LIKE '%Full%') OR (BINARY MyField2 LIKE '%Relevance%')) UNION SELECT * FROM MyTable WHERE ((MyField LIKE '%Full%') OR (MyField LIKE '%Relevance%')) OR ((MyField2 LIKE '%Full%') OR (MyField2 LIKE '%Relevance%'))
Конечно, уровни релевантности при поиске по несколькими полям/таблицам могут быть уточнены таким образом, чтобы отдельные поля и/или таблицы имели приоритет, но это уже частные доработки.
Вот, в общем-то, и всё. Добавлю лишь, что попутно мне пришлось решать ещё одну, довольно стандартную проблему – постраничный вывод результатов поиска. Принять правильное решение по этому вопросу мне помогло одно обсуждение на форумах XPoint.ru.
Благодарность за стимул в подготовке данной статьи выражается Артёму Петрову.