Recherche Fulltext

Recherche Fulltext

2 July 2021

La recherche Fulltext est un élément vraiment intéressant en SQL, dès que l'on veut intégrer un moteur de recherche sur son site. Évidemment, il ne s'agit pas de concurrencer Google ou les autres. Cependant, la recherche Fulltext sur son propre site peut s'avérer tout à fait suffisante.

Le principe de cette recherche est de classer les résultats par score selon la pertinence des données trouvées.

L'avantage d'une recherche en Fulltext est qu'elle se fonde sur l'index des mots en retirant tous ceux qui sont les plus communs (le, car, je...). De là, elle établira une liste des mots indexés avec la position de ceux-ci dans la base de données et pourra obtenir une évaluation de leur importance.

Par la suite, au lieu d'effectuer une recherche dans la base de données, celle-ci s'effectuera dans un premier temps dans la liste de mots indexés, afin de retourner une première, réponse et terminera en effectuant une recherche normale selon les réponses trouvées.

Ce mode de recherche est bien plus efficace pour obtenir une réponse rapide sur de nombreuses données qu'une simple requête SQL de type LIKE.

Tous les index sont actualisés automatiquement à chaque mouvement dans la table concernée.

Création d'un index

Avant de créer votre moteur de recherche, vous devez préciser quels champs de votre table SQL seront indexés.

Je crée une table qui nous servira d'exemple tout au long de cet article :

CREATE TABLE articles(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(150),
  content TEXT,
  created_at DATETIME,
  updated_at DATETIME
);

Maintenant, j'indexe les champs title et content de ma table afin d'effectuer les recherches.

Attention ! Seuls les champs de types CHARVARCHAR et TEXT peuvent être utilisés pour construire un index.

ALTER TABLE articles ADD FULLTEXT INDEX search (title, content);

Si jamais lors de la création d'index, vous rencontrez l'erreur suivante : Column 'foobar' cannot be part of FULLTEXT index, c'est que les champs indexés n'ont pas le même jeu de caractères charset et le même interclassement collation.

Maintenant que notre table SQL est prête, nous pouvons passer à la recherche Fulltext.

Requête SQL de recherche

Pour la requête SQL, nous utiliserons la fonction MATCH AGAINST :

SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('recherche voiture');

Cette requête va chercher tous les articles contenant les mots recherche et voiture dans les colonnes titlecontent et classera les résultats par pertinence.

BOOLEAN MODE

Le BOOLEAN MODE permet d'introduire différents opérateurs afin d'obtenir une recherche avancée.

Les opérateurs sont :

  • + : Le mot doit obligatoirement être présent.
  • - : Le mot ne doit pas être présent.
  • >< : Augmente ou diminue l'importance d'un mot.
  • ~ : Agis comme l'opérateur -, mais sans obligation d'exclusion.
  • () : Les parenthèses regroupent les mots en sous-expressions.
  • * : C'est un joker wildcard. La recherche jardin donnera naissance à jardins, jardinier, jardiner...
  • "" : Effectue une recherche exacte des mots entourés des guillemets doubles.

Reprenons la requête de recherche ci-dessus et ajoutons ce mode ainsi que quelques opérateurs :

SELECT *
FROM articles
WHERE MATCH(title, content) AGAINST('-recherche +voiture' IN BOOLEAN MODE);

Cette requête va chercher tous les articles contenant le mot voiture mais pas le mot recherche dans les colonnes title, content et classera les résultats par pertinence.

Classer par score

La pertinence des résultats est choisie par la recherche Fulltext et est classée selon un score. Vous avez la possibilité de voir ce score et même de choisir à partir de quel score les résultats vous intéressent.

Commençons par récupérer le score de la recherche :

SELECT *, MATCH(title, content) AGAINST('-recherche + voiture' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('-recherche + voiture' IN BOOLEAN MODE);

Dans le SELECT, nous ajoutons un MATCH AGAINST, le même que celui présent dans le WHERE, et nous lui donnons un alias, ici score.

Une nouvelle colonne fera son apparition contenant le score déterminé par la recherche, il s'agit généralement d'une décimale à 12 chiffres après la virgule.

Maintenant, nous pouvons demander à retrouver uniquement les résultats dont le score est supérieur à 2 :

SELECT *, MATCH(title, content) AGAINST('-recherche + voiture' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('-recherche + voiture' IN BOOLEAN MODE) > 2;

Enfin, si vous souhaitez aussi ranger les résultats par score, vous pouvez toujours utiliser le ORDER BY en jouant sur l'alias précédemment créé :

SELECT *, MATCH(title, content) AGAINST('-recherche + voiture' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('-recherche +voiture' IN BOOLEAN MODE) > 2
ORDER BY score ASC;

À savoir

La recherche Fulltext fonctionne uniquement dans les tables SQL utilisant le moteur MyISAM. Si vous avez choisi le moteur InnoDB, cela ne fonctionnera pas.