MySQL match() against() - order by relevance and column?

Question

D'accord, je suis en train d'essayer de faire une recherche de texte intégral dans plusieurs colonnes, quelque chose de simple comme ceci :

SELECT * FROM pages WHERE MATCH(head, body) AGAINST('quelques mots' IN BOOLEAN MODE)

Maintenant, je veux trier par pertinence, (combien des mots sont retrouvés?) ce que j'ai pu faire avec quelque chose comme ceci :

SELECT * , MATCH (head, body) AGAINST ('quelques mots' IN BOOLEAN MODE) AS pertinence 
FROM pages
WHERE MATCH (head, body) AGAINST ('quelques mots' IN BOOLEAN MODE)
ORDER BY pertinence

Maintenant vient la partie où je suis perdu, je veux donner la priorité à la pertinence dans la colonne head.

Je suppose que je pourrais créer deux colonnes de pertinence, une pour head et une pour body, mais à ce moment-là, je ferais en quelque sorte la même recherche dans la table trois fois, et pour ce pour quoi je fais cette fonction, les performances sont importantes, car la requête sera à la fois jointe et comparée à d'autres tables.

Alors, ma question principale est, y a-t-il un moyen plus rapide de rechercher la pertinence et de donner la priorité à certaines colonnes ? (Et en bonus, éventuellement faire en sorte que la pertinence compte le nombre de fois où les mots apparaissent dans les colonnes ?)

Toutes suggestions ou conseils seraient les bienvenus.

Note : Je vais exécuter ceci sur un serveur LAMP. (WAMP en test local)

Answer 1

Cela pourrait donner la pertinence accrue à la partie en-tête que vous souhaitez. Cela ne la doublera pas, mais cela pourrait être assez bon pour vous :

SELECT pages.*,
       MATCH (head, body) AGAINST ('some words') AS relevance,
       MATCH (head) AGAINST ('some words') AS title_relevance
FROM pages
WHERE MATCH (head, body) AGAINST ('some words')
ORDER BY title_relevance DESC, relevance DESC

-- ou bien :
ORDER BY title_relevance + relevance DESC

Une alternative que vous voudrez également envisager, si vous avez la flexibilité de changer de moteur de base de données, est Postgres. Il permet de définir le poids des opérateurs et de jouer avec le classement.

Answer 2

Juste ajouté pour ceux qui pourraient en avoir besoin.. N'oubliez pas de modifier la table!

ALTER TABLE nom_de_la_table AJOUTER FULLTEXT(nom_de_la_colonne);

Answer 3

Je ne l'ai jamais fait, mais il semble que

MATCH (head, head, body) AGAINST ('some words' IN BOOLEAN MODE)

Doit donner un poids doublé aux correspondances trouvées dans l'en-tête.

---

Venez de lire ce commentaire sur la page docs, je pensais que cela pourrait vous être utile :

Posté par Patrick O'Lone le 9 décembre 2002 à 6h51

Il convient de noter dans la documentation que IN BOOLEAN MODE retourne presque toujours une pertinence de 1.0. Pour obtenir une pertinence significative, vous devrez :

SELECT MATCH('Contenu') AGAINST ('motclé1 motclé2') as Pertinence 
FROM table 
WHERE MATCH ('Contenu') AGAINST('+motclé1+motclé2' IN BOOLEAN MODE) 
HAVING Pertinence > 0.2 
ORDER BY Pertinence DESC 

Remarquez que vous effectuez une requête de pertinence normale pour obtenir les facteurs de pertinence combinés avec une clause WHERE qui utilise le MODE BOOLEAN. Le MODE BOOLEAN vous donne le sous-ensemble qui répond aux exigences de la recherche BOOLEAN, la requête de pertinence répond au facteur de pertinence, et la clause HAVING (dans ce cas) garantit que le document est pertinent pour la recherche (c'est-à-dire que les documents qui obtiennent un score inférieur à 0.2 sont considérés comme non pertinents). Cela vous permet également de trier par pertinence.

Cela peut ou non être un bug dans le fonctionnement du MODE BOOLEAN, bien que les commentaires que j'ai lus sur la mailing list suggèrent que le classement de pertinence du MODE BOOLEAN n'est pas très compliqué, se prêtant donc mal à fournir effectivement des documents pertinents. BTW - Je n'ai pas remarqué de perte de performances en faisant cela, car il semble que MySQL ne réalise la recherche FULLTEXT qu'une seule fois, même si les deux clauses MATCH sont différentes. Utilisez EXPLAIN pour le prouver.

Il semblerait donc que vous n'ayez pas besoin de vous soucier d'appeler la recherche plein texte deux fois, bien que vous devriez toujours "utiliser EXPLAIN pour le prouver"

Answer 4

J'étais juste en train de jouer avec ça aussi. Une façon d'ajouter du poids supplémentaire est dans la zone ORDER BY du code.

Par exemple, si vous faisiez correspondre 3 colonnes différentes et que vous vouliez donner plus de poids à certaines colonnes :

SELECT search.*,
MATCH (name) AGAINST ('black' IN BOOLEAN MODE) AS name_match,
MATCH (keywords) AGAINST ('black' IN BOOLEAN MODE) AS keyword_match,
MATCH (description) AGAINST ('black' IN BOOLEAN MODE) AS description_match
FROM search
WHERE MATCH (name, keywords, description) AGAINST ('black' IN BOOLEAN MODE)
ORDER BY (name_match * 3  + keyword_match * 2  + description_match) DESC LIMIT 0,100;

Answer 5

Juste pour ajouter que si vous utilisez un classement personnalisé, n'oubliez pas d'utiliser HAVING au lieu de WHERE pour réduire la charge.

SELECT MATCH(x,y) AGAINST (? IN BOOLEAN MODE) AS r1,
MATCH(z) AGAINST (? IN BOOLEAN MODE) AS r2,
...
FROM table 
HAVING (r1 + r2) > 0
ORDER BY (r1 * 3 + r2) DESC
LIMIT 10