Как отсортировать результаты полнотекстового поиска MYSQL по релевантности

Я относительно новичок в MYSQL, и у меня была проблема, которая несколько время подтачивала меня. Я попробовал поиск по всему сайту для ответа, но пока не смог найти приемлемого решения.

Вот запрос, который я выполняю в настоящее время, чтобы найти наилучшее совпадение для данного поискового термина:

$query="SELECT * from `vocabulary` WHERE translation = 'word' OR translation LIKE '%word%'";

Результаты, которые он возвращает, являются всеобъемлющими, поскольку они включают все соответствующие строки. Тем не менее, они не сортируются в каком-либо конкретном порядке, и я хотел бы иметь те, с точным совпадением, отображаемым первым, когда я печатаю результаты в PHP. Вот так:

1 | word < -exact match
2 | crossword < - частичные совпадения, отсортированные в алфавитном порядке /
3 | слова
4 | wordmith

Заранее благодарю вас за помощь.

-macspacejunkie

Ответ 1

SELECT * from vocabulary 
WHERE translation like 'word'  
union all
SELECT * from vocabulary 
WHERE translation LIKE '%word%' and translation not like 'word'

будет отображать точные совпадения в первую очередь

Ответ 2

LIKE не полнотекстовый поиск. В полнотекстовом поиске MATCH(...) AGAINST(...) возвращает сопоставимую оценку, которая может быть приблизительно аппроксимирована как релевантность.

Ответ 3

Вы можете получить хороший поиск релевантности, создав полный текст, а затем сопоставляя его с поисковым запросом.

Итак, что-то вроде этого должно работать.

ALTER TABLE `vocabulary` ADD FULLTEXT INDEX `SEARCH`(`translation`);

SELECT *, MATCH(translation) AGAINST ('+word' IN BOOLEAN MODE) AS relevance 
FROM `vocabulary`
WHERE MATCH(translation) AGAINST ('+word' IN BOOLEAN MODE)
ORDER BY relevance DESC

Подробнее об этом можно узнать в Справочном руководстве по MySQL.

Ответ 4

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

Я делаю два оператора MATCH() AGAINST() в выборе и объединяю оценку от каждого, чтобы сформировать общую релевантность. Назначение разных множителей позволяет мне настроить импорт каждого набора результатов.

Мой первый MATCH() будет проверять буквальный (или точный) поисковый запрос, используя двойные кавычки Мой второй MATCH будет нормально проверяться. Я применяю более высокий множитель к первому совпадению, поэтому он должен иметь более высокое значение релевантности, если он найден.

Что-то вроде этого.

SELECT *, ((MATCH(indexes) AGAINST ('"search_terms"' IN BOOLEAN MODE) * 10)  
           + (MATCH(indexes) AGAINST ('search_terms' IN BOOLEAN MODE) * 1.5)) AS relevance  
FROM ...
WHERE ...  
      AND (MATCH (indexes) AGAINST ('"search_terms"' IN BOOLEAN MODE) > 0  
           OR MATCH (indexes) AGAINST ('search_terms' IN BOOLEAN MODE) > 0)  
      ...
ORDER BY relevance DESC

Если вы используете функцию EXPLAIN, чтобы показать, как работает запрос, вы должны обнаружить, что дополнительные предложения MATCH() AGAINST() фактически не добавляют каких-либо накладных расходов на запрос из-за того, как работает MySQL.

Ответ 5

Ваш запрос нуждается лишь в небольшом изменении, чтобы получить заказ, который вы ищете.

SELECT * 
FROM vocabulary
WHERE translation LIKE '%word%'
ORDER BY translation <> 'word', translation;

Если translation точно 'word', оно будет в верхней части результатов. Это связано с тем, что translation <> 'word' будет 0, когда есть точное соответствие, которое предшествует 1, которое будет возвращено для всех других результатов. Остальные результаты будут отсортированы в алфавитном порядке после этого из-за , translation.

Этот запрос позволяет избежать двух запросов, таких как выбранный ответ, с его UNION. Кроме того, ваш запрос не нуждается в translation = 'word' OR translation LIKE '%word%', так как вторая половина всегда будет выполняться и является надмножеством первой части.

Для тех, кто ищет ответ, который использует фактический полнотекстовый поиск, см. другие, более высокоподдерживаемые ответы.