Полнотекстовый поиск с использованием булевых операторов MySQL с тегами

Забавно, это 3-й вопрос почти о той же проблеме, которую я вижу через 2 дня, посмотрите эти два сообщения: 1 , 2

Начиная с ответа @ james.c.funk, но с некоторыми изменениями.

SELECT a.id, a.title, 
  MATCH (a.title) AGAINST (?) AS relevance
FROM articles AS a
LEFT OUTER JOIN (articles_tags AS at
  JOIN tags AS t ON (t.id = at.tag_id AND t.name = ?))
  ON (a.id = at.article_id)
WHERE MATCH (a.title) AGAINST (? IN BOOLEAN MODE) 
ORDER BY IF(t.name IS NOT NULL, 1.0, relevance) DESC;

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

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

Логический режим заставляет MATCH () возвращать 1.0 при совпадении, что делает его бесполезным в качестве мера актуальности. Поэтому проведите дополнительное сравнение в списке выбора, чтобы вычислить релевантность. Это значение составляет от 0,0 до 1,0. Теперь мы можем повысить сортировку соответствия тегов, рассматривая его как имеющий релевантность 1.0.

Этот быстрый демонстрационный запрос далек от оптимизации, но должен стать хорошей отправной точкой

SELECT * FROM
(SELECT a.id, a.title, 
  MATCH (a.title) AGAINST ('$s_search_term') AS title_score,
  SUM(MATCH (t.name) AGAINST ('$s_search_term')
) AS tag_score
FROM articles AS a
LEFT JOIN articles_tags AS at
  ON a.id = at.article_id
LEFT JOIN tags AS t
  ON t.id = at.tag_id
WHERE MATCH (a.title) AGAINST ('$s_search_term') 
  OR MATCH (t.name) AGAINST ('$s_search_term')
GROUP BY a.id) AS table1
ORDER BY 2*tag_score + title_score DESC

Вы можете нормализовать tag_score, разделив его на COUNT (t.id). Извините, но легче задать вопрос, чем объяснить, как это сделать.

Вы можете захотеть посмотреть в SPHINX, http://www.sphinxsearch.com/

Вот как я сделал это в прошлом. Это выглядит медленно, но я думаю, что вы найдете это не так.

Я добавил немного сложности, чтобы показать, что еще можно легко сделать. В этом примере статья получит 1 точку для частичного матча заголовка, 2 балла для частичного матча тегов, 3 балла за точное совпадение тега и 4 точка для точного совпадения заголовка. Затем он добавляет те и сортировки по оценке.

SELECT
  a.*,
  SUM(
    CASE WHEN a.title LIKE '%keyword%' THEN 1 ELSE 0 END
    +
    CASE WHEN t.name LIKE '%keyword%' THEN 2 ELSE 0 END
    +
    CASE WHEN t.name = 'keyword' THEN 3 ELSE 0 END
    +
    CASE WHEN a.title = 'keyword' THEN 4 ELSE END
  ) AS score
FROM article a, articles_tags at, tags t
WHERE a.id = at.article_id
AND at.tag_id=t.id
AND (a.title LIKE '%keyword%' OR t.name LIKE '%keyword%')
GROUP BY a.id
ORDER BY score;

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

Стоит ли в данный момент смотреть на разгрузку поискового задания на что-то, что на самом деле написано именно для этой цели?

В наших продуктах мы используем MySQL для хранения данных, но индексируем все наши данные с помощью Lucene (через Solr - но это не относится к делу).

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

Извините, что это не прямой ответ на вопрос, я просто чувствую, что такие вещи всегда стоит упоминать в этом сценарии :)