Trier par pertinence avec MySQL

lundi 14 novembre 2011 :: perrick :: MySQL :: 5 commentaires :: aucun trackback

Ce n'est pas tous les jours que je découvre des petits trucs avec MySQL et la synaxe SQL en général. Alors voici ma dernière production :

SELECT `contact`.* FROM contact WHERE (contact.lastname LIKE '%gra%' OR SOUNDEX(contact.lastname) LIKE SOUNDEX('gra') OR contact.firstname LIKE '%gra%' OR SOUNDEX(contact.firstname) LIKE SOUNDEX('gra') OR contact.company LIKE '%gra%' OR SOUNDEX(contact.company) LIKE SOUNDEX('gra')) LIMIT 0,10

Cette requête permet de sélectionner des contacts en fonction de leur nom, de leur prénom ou de leur entreprise, mais aussi avec une orthographe approchante via la fonction SOUNDEX. Elle marche, elle est en production depuis plusieurs années. Seul problème : le tri, surtout quand on trouve plus de 10 réponses. Comment distinguer facilement les réponses en orthographe précise de celles qui s'en approchent seulement ?

Et puis aujourd'hui j'ai trouvé ça : MySQL - order by relevance. Dans mon cas, le requête SQL devient :

SELECT `contact`.* FROM contact WHERE (contact.lastname LIKE '%gra%' OR SOUNDEX(contact.lastname) LIKE SOUNDEX('gra') OR contact.firstname LIKE '%gra%' OR SOUNDEX(contact.firstname) LIKE SOUNDEX('gra') OR contact.company LIKE '%gra%' OR SOUNDEX(contact.company) LIKE SOUNDEX('gra')) ORDER BY (CASE WHEN lastname LIKE 'gra%' THEN 3 ELSE 0 END) + (CASE WHEN firstname LIKE 'gra%' THEN 2 ELSE 0 END) + (CASE WHEN company LIKE 'gra%' THEN 1 ELSE 0 END) DESC LIMIT 0,10

En utilisant des calculs dans la clause ORDER BY et en les cumulant avec des CASE, j'arrive à donner la priorité aux mots qui commencent par mon motif de recherche... Mon petit bonheur du jour ! A partager.

Vos commentaires et/ou trackbacks

Le lundi 14 novembre 2011 à 13:18, commentaire par Moosh :: site :: #

Juste pour dire que quand ca devient "trop lourd".

sphinx est une solution d'indexation assez efficace.

Elle permet des recherches avancées avec tri par pertinence, expression à la goole, avec des performance en recherche 100X plus rapide que le fulltext de mysql. Autre chose, pour ta requete , le soundex est relativement couteux et la chaine source varie peut. MariaDB et ses colonnes calculées peuvent être d'une grande aide.

Le lundi 14 novembre 2011 à 13:47, commentaire par Eroan :: site :: #

Très intéressant, je bookmarke au cas où. Par contre ça doit être assez moyen niveau perfs (j'entends en temps de réponse) sur une base de donnée de plusieurs milliers de lignes non ?

À voir concrètement sur une appli ou un site en prod ;)

Le lundi 14 novembre 2011 à 14:22, commentaire par Ghusse :: site :: #

À noter également à propos de Soundex:

<blockquote>This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results</blockquote>

Le lundi 14 novembre 2011 à 15:34, commentaire par Jean-Marc Fontaine :: site :: #

Je rejoins l'avis de Moosh.

Des outils comme Solr ou Sphinx sont beaucoup plus adaptés à ce genre de choses.

Le lundi 14 novembre 2011 à 18:13, commentaire par perrick :: site :: #

@moosh et @jmf, j'ai bien connaissance des limites de cette construction, mais j'ai aussi et surtout une très bonne connaissance de mes utilisateurs. Opentime n'a pas besoin d'être une bête de compétition au niveau des perfs. A partir du moment que c'est "acceptable". Il y a beaucoup plus de contraintes sur la facilité d'installation : je me vois mal demander Solr ou Sphinx en plus du couple PHP / MySQL.

Ajouter un commentaire

Les commentaires pour ce billet sont fermés.