Recherche & Pertinence avec MySQL & FULLTEXT - InnoDB

L’utilisation d’un moteur de recherche comme Google ne génère pas que des résultats, mais aussi des habitudes. Les apparences sont trompeuses, tous les couples « champ de formulaire / loupe » ne se valent pas et force est de constater que les outils de recherche interne de la plupart des sites génèrent des résultats plus ou moins pertinents, voire aléatoires.

Si vous n’avez pas le même budget que Google pour optimiser la pertinence de l'outil de recherche de votre e-commerce, il existe cependant des solutions afin d'augmenter les chances que votre client internaute puisse trouver et commander chez vous l’objet de ses désirs.

Désolé, votre recherche ne donne aucun résultat !

Sur Google, il faut vraiment le faire exprès pour obtenir un message de ce genre et pourtant même Google prévoit une page avec quelques conseils :

Profitez-en pour battre Google (oui là c’est possible) et soyez par le texte ce vendeur efficace toujours au service de ses clients (et de son CA) :

Nous vous invitons à réessayer avec moins ou d'autres mots-clés, à vérifier l’orthographe…
Vous pouvez également utiliser les menus du site…
N'hésitez pas à nous contacter, nous répondrons à toutes vos questions, etc.

C’est le service minimum, mais vous pouvez faire mieux sur le plan du marketing, y mettre un peu de l'ADN de votre entreprise, etc.
Ces conseils peuvent s'appliquer également aux pages « introuvables » 404.

Un jour une cliente m’appelle :

« Jean-Marc, je ne comprends pas, lorsque je saisis « table » dans l’outil de recherche de ma boutique, il me sort un "sac à main", un peu de n’importe quoi et puis une ou deux tables, en bas de page ! »

Je saisis donc "table" et constate effectivement la présence du maudit sac à main en deuxième position ! Comme j’ai une idée du pourquoi du comment, je clique, direction la fiche produit du sac, CTRL F sur "table" et bingo je trouve "cuir véritable" en bas de page, perdu dans la description longue !

J'appelle Florian mon développeur préféré et il me dit :

« sur ce site, on cherche juste si il y a un bout de chaîne de caractères qui "match" avec le nom du produit, la description courte ou longue, la référence et on sort les résultats... »

Peux mieux faire ?

Compatissant devant la déception de son chef de projet préféré, il me parle alors de l’association FULLTEXT/MySQL qu’il n’a pas le temps de prendre en charge (à cause de celle que je lui ai déjà mise sur le dos ; ) et qui jadis marchait mal ou pas avec Innobd, etc.

Une semaine plus tard, nous avions enfin des résultats plus pertinents à offrir à nos clients.

TUTO : MY SQL IS RICH (with FULLTEXT & InnoDB)

Partageons avec vous, cette petite aventure, des bouts de code et quelques pistes pour prendre en mains cette solution dans la joie et la bonne humeur.

Je précise avant tout :

  • Que ce tuto s’adresse à nos chers développeurs (PHP/MySQL).
  • Que la mise en œuvre « chez nous » est somme toute récente, mais quelle a nettement amélioré l’efficacité des nos outils de recherche interne.
  • Que l’auteur se débrouille, mais n’est pas un expert dans ce domaine.
  • Que les commentaires des experts en la matière sont donc les bienvenus.
  • Que ce premier tuto "FULLTEXT" n'est qu'une petite introduction et aura une suite plus avancée avec notamment l'implémentation du mode "BOOLEAN".

GO SUR LA CONSOLE MySQL (version utilisée V 5.7) :

Créez une base de données « mabase_test_fulltext » interclassement « utf8_general_ci »
et une table « produits » (moteur de stockage « Innobd ») avec 3 champs :

CREATE DATABASE IF NOT EXISTS `mabase_test_fulltext` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `mabase_test_fulltext`;

CREATE TABLE `produits` (
  `id` int(10) UNSIGNED NOT NULL,
  `nom` varchar(96) NOT NULL,
  `description_courte` text NOT NULL,
  `description_longue` mediumtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Ajouter les index FULLTEXT aux champs dans lesquels vous souhaitez rechercher :

ALTER TABLE `produits` ADD FULLTEXT ft_nom (nom);
ALTER TABLE `produits` ADD FULLTEXT ft_description_courte (description_courte);
ALTER TABLE `produits` ADD FULLTEXT ft_description_longue (description_longue);

Deux exemples de produits pour tester :

INSERT INTO `produits` (`id`, `nom`, `description_courte`, `description_longue`)
VALUES (1, 'Article truc', 'truc', 'truc machin');
INSERT INTO `produits` (`id`, `nom`, `description_courte`, `description_longue`)
VALUES (2, 'Produit machin', 'machin', 'machin');

La requête relative à la recherche (en mode "NATURAL" (non spécifié, car par défaut)) :

SELECT
nom,
description_courte,
description_longue,
MATCH(nom) AGAINST('article machin')  AS  score_nom,
MATCH(description_courte) AGAINST('article machin') AS score_description_courte,
MATCH(description_longue) AGAINST('article machin') AS score_description_longue
FROM produits
WHERE
MATCH(nom) AGAINST('article machin') OR
MATCH(description_courte) AGAINST('article machin') OR
MATCH(description_longue) AGAINST('article machin')
ORDER BY (score_nom+score_description_courte*0.5+score_description_longue*0.1) DESC;

Avant de vous donner quelques explications sur le code, si vous exécutez la requête précédente vous obtenez ça :

Grâce à FULLTEXT, nous bénéficions de la notion de score qui est relative au niveau de pertinence calculée par MySQL. Chaque produit obtient par conséquent un score pour chaque champ que l'on additionne pour le classement global des produits via le "ORDER BY".

Je vous invite à faire moult essais, avec des mots-clés différents, avec plus de produits, etc.

Explications relatives au code :

Dans le SELECT vous récupérez le score de chaque champ qui "MATCH" avec les mots-clés, avec "AS le_score_du_champ"

MATCH(nom) AGAINST('article machin') AS score_nom

Grâce au WHERE on ne retourne que les produits dont au moins un des scores de chaque champ qui "MATCH" avec les mots-clés est supérieur à 0

WHERE
MATCH(nom) AGAINST('article machin') OR
MATCH(description_courte) AGAINST('article machin') OR
MATCH(description_longue) AGAINST('article machin')

Dans le ORDER BY :

ORDER BY (score_nom+score_description_courte*0.5+score_description_longue*0.1) DESC;

Le score total du produit est une addition des scores de chaque champ qui "match" avec les mots-clés.
Pour donner plus ou moins de poids à un champ en particulier, il suffit de lui affecter un coefficient. Dans l'exemple, la description courte (score_description_courtex0.5) voit son score divisé par 2 par rapport au nom du produit et par 10 pour la description longue (x0.1). Ceci dans le but d'équilibrer la recherche et que sortent avant tout des noms de produits cohérents avec la requête saisie. On tient compte du fait que l'on peut rencontrer plusieurs fois un ou plusieurs des mots-clés saisis dans les descriptions et que cela affecterait (trop) les résultats en leur faveur.

Testez par vous-même en modifiant les coefficients "0.5" ou "0.1" ainsi qu'en jouant avec les mots-clés placés ici ou là...

Note : multiplier un des scores par zéro ne serait pas une bonne idée, car mieux vaut dans ce cas ne pas chercher du tout (ni select, ni where).
Plage recommandée : 0 < score 1

MySQL même avec FULLTEXT n'est pas Google (celui d'aujourd'hui) alors bien sûr le nombre de fois où le mot-clé apparaît dans le nom ou une description est un des facteurs clé pour le calcul par MySQL du score et donc de la la pertinence. Cela va rappeler des souvenirs aux pionniers du référencement (n'est-ce pas Sylvain). Alors, amis SEO depuis trop longtemps Pandatisés, Pingouintisés, lâchez-vous le temps d'un test, faites-vous plaisir sans modération et sans risque de pénalité en "bourrinant" de mots-clés comme au bon vieux temps ; )

Plus sérieusement, une fois informé, l'administrateur d'un site web ou d'un e-commerce pourra à défaut de s'offrir un outil de recherche plus évolué (plus cher), s'organiser, exemple : opter pour ne pas rechercher dans la description longue et faire plus attention dans la rédaction de ses descriptions courtes, des noms des produits, etc. On peut envisager toujours à moindres frais, un champ dédié uniquement aux mots-clés pour chaque produit. Ce champ bien évidemment n'apparaissant pas sur le site et servant uniquement à monter le niveau de pertinence.

TUTO en mode "BOOLEAN" : la suite au prochain épisode

Dans un prochain article, je vous proposerai le partage d'une solution beaucoup plus avancée. Nous verrons ensemble le mode "BOOLEAN" qui permet d'aller encore plus loin et d'utiliser des "operators" de type "wildcard * + -" et plein d'autres surprises...

Dans ce futur tuto, la requête SQL sera codée côté PHP de façon a y inclure, un filtre, les variables, les conditions nécessaires au pilotage et aux ajustements possibles de l'outil.

La suite publiée : TUTO : MYSQL FULLTEXT IN BOOLEAN MODE

Quelques sources d'information :

En espérant avoir suscité des envies de bricolage numérique, l'indulgence des experts en la matière et dans l'attente de vos pertinents commentaires, merci.

Spéciale dédicace : l'illustration en haut de page est l’œuvre de notre graphiste préférée Nathalie.

(je recevrai un mail quand un article est publié (no spam)

7 thoughts on “Recherche & Pertinence avec MySQL & FULLTEXT - InnoDB

  1. Mitsu

    Bonjour,

    Très intéressant pour de petit besoins, néanmoins sur de gros volumes, le mieux étant encore d'utilise une solution search du marché.
    Notamment Elastic Search qui est gratuit et très rapide. Bon après faut le mettre en place, on est bien d'accord.

  2. Antoine

    Hello,
    Merci pour ce tuto, qui me permettra de faire des recos sur le moteur de recherche interne à mes clients.
    Petite question : à quoi sert la base créée au début, "mabase_test_fulltext" ?
    Antoine

  3. Chartier Mathieu

    Bravo pour cet article, c'est très bien expliqué en effet pour la base.
    Toutefois, tu as juste oublié un paramètre important relatif à la recherche FULLTEXT, il convient d'avoir un serveur bien paramétré. Pour tout ceux qui utilisent des hébergements mutualisés (et ils sont nombreux), il est impossible de modifier quelques paramètres qui permettent d'aller plus loin avec la recherche FULLTEXT. Je prends l'exemple le plus courant, FULLTEXT ne prend pas en compte les recherches des mots de 3 lettres et moins par défaut, donc on ne peut pas chercher "SEO" par exemple, Sylvain risque de râler. ^^

    J'entends également qu'il vaut mieux passer par ElasticSearch ou équivalent, mais ce n'est pas toujours vrai. Ce sont d'excellentes technologies, mais ElasticSearch est fait en Java, et on ne peut pas dire que ce langage soit bien plus rapide que PHP 7 pour le traitement des recherches. Il est plus rapide pour indexer les pages, mais pas forcément pour les restituer ensuite. C'est plutôt le côté asynchrone qui donne la sensation de vitesse et de précision dans ce cas. Je préconise plutôt robot d'indexation en Java ou Python, puis une restitution web des résultats avec PHP 7. D'ailleurs, Google fonctionne un peu ainsi, ce ne sont pas les mêmes technologies utilisées selon les besoins. 😉

  4. jm Auteur de l’article

    @Mathieu
    Merci Mathieu pour le compliment, ça mérite un lien vers ton mega-super tuto : "Moteur de recherche PHP complet (pagination, surlignage, fulltext)".
    C'est Florian chez nous qui s'occupe de l'infogérance de nos serveurs dédiés et sans lui avoir demandé les mots de 3 lettres passent très bien, exemple : https://www.babystock.fr/s/1,0/1.html?c=0&s=top
    Le site de notre cliente babystock.fr bénéficie du mode BOOLEAN + Wilcard (paramétré en auto *). Je t'invite à jeter un coup d’œil à mon tuto plus avancé : "TUTO : MYSQL FULLTEXT IN BOOLEAN MODE" (si tu ne l'as pas déjà parcouru).
    J'ai regardé le code de ton "moteur-php5.5.class-inc.php" et comme nous avons une approche différente pour la gestion des wildcards, nettoyer les operators (mal placés ou doublonnés qui feraient planter la requête), j'aimerais avoir ton avis sur ma Regex de filtrage. J'en profite d'ailleurs à ce niveau pour dégager via PHP les mots inférieurs à 3 lettres.
    Merci encore pour les autres pistes (ElasticSearch, etc.) qui pourront être utiles à celles et ceux qui sont en mode recherche !

Partagez sur :

Les commentaires sont fermés.