MySQL et la recherche textuelle

Beaucoup de SGBD implémentent un mode de recherche dite textuelle, où il est possible de rechercher un mot ou une phrase dans une base de données, sur plusieurs champs en effectuant un tri sur la pertinence du mot recherché. Ce filtrage diffère de l'opérateur LIKE qui ne retrouve que la présence de la chaîne sur un champ.

MySQL implémente la recherche « FULL TEXT » depuis la version 3.23.23. Cet article se borne à vous fournir les bases pour utiliser ce type de recherche.

La recherche en plein texte existe dans MySQL depuis la version 3.23.23. Elle a connu une nette amélioration avec la version 4. Par défaut, elle ne nécessite pratiquement pas d'administration.

En effet, seules les versions 4.0.10 et supérieures de MySQL permettent de contrôler le comportement de la recherche textuelle. Cinq variables sont à la disposition de l'administrateur.

La modification d'une ou plusieurs options oblige à reconstruire les index de recherche textuelle.

La reconstruction des index « full text » s'effectue suivant deux méthodes distinctes. Vous pouvez supprimer l'index « full text » sur la table puis le recréer. Soit pour des tables très importantes, utiliser REPAIR TABLE Nom de la table EXTENDED.

Enfin, toutes les versions de MySQL supportant la recherche textuelle peuvent être recompilées pour prendre en compte une liste de mots interdits personnalisée. Cela se fait en modifiant le fichier ‘myisam/ft_static.c'.

/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB */
…
#ifdef COMPILE_STOPWORDS_IN
/* This particular stopword list was taken from SMART distribution
ftp://ftp.cs.cornell.edu/pub/smart/smart.11.0.tar.Z
it was slightly modified to my taste, though
*/

  "a",
"a's",
"able",

…

  "yourself",
"yourselves",
"z",
"zero"

#endif

Pour pouvoir ajouter ou modifier des mots dans cette liste, il suffit de rajouter le mot entre guillemets droits doubles.

La désactivation de l'option de tri stoppant la recherche si un mot renvoie plus de la moitié des lignes dans la table se fait en remplaçant l'option GWS_PROB par GWS_FREQ sur la définition de la variable GWS_IN_USE dans le fichier 'myisam/ftdefs.h', ce qui demande là aussi une compilation.

/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB */

…

…

#define NORM_IN_USE                         NORM_PIVOT
#define GWS_IN_USE                          GWS_PROB

/*==============================================================*/

#define LWS_TF                                      (count)
#define LWS_BINARY                          (count>0)

…

De même, il est possible de modifier la limite supérieure de la taille des mots à indexer. Dans 'myisam/fulltext.h', modifier la définition suivante « #define HA_FT_MAXLEN 254 ».

Les index de type « full text » ne sont disponibles qu'avec les tables de type MyIsam. La création d'un index « full text » peut se faire lors de la création de la table ou lors de son altération.

CREATE TABLE example_fulltext(
    idexample_fulltext INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    date_texte DATE NOT NULL,
    titre VARCHAR(200) NULL,
    auteur VARCHAR(30) NULL,
    commentaire TINYTEXT NULL,
    texte TEXT NULL,
    validite BOOL NULL,
    PRIMARY KEY(idexample_fulltext),
    FULLTEXT INDEX example_fulltext_plein(texte, commentaire, titre),
    INDEX example_fulltext_auteur(auteur)
);

ALTER TABLE example_fulltext
ADD FULLTEXT nom_index (texte, commentaire, titre)

Pour pouvoir indexer plusieurs colonnes à la fois, il est primordial de n'utiliser qu'un seul index. La création de plusieurs index de type « Full Text » est inutile, cela peut ralentir grandement les performances de votre serveur.

La recherche textuelle s'effectue suivant deux méthodes. Une méthode qualifiée de simple et la méthode booléenne.

Les deux méthodes utilisent les fonctions :

MATCH ( colonnes ) AGAINST ( chaîne recherchée )
SELECT   titre, MATCH (texte, commentaire, titre) AGAINST ( 'test' )
FROM  example_fulltext

Cette fonction renvoie un décimal correspondant à la pertinence de la chaîne recherchée dans les colonnes de la recherche. Un résultat nul est renvoyé lorsque le mot n'a pas été rencontré dans la table.

De la même façon, si aucun résultat supérieur à 0 n'est renvoyé, cela peut signifier qu'aucune ligne de la table ne contient la chaîne recherchée ou que la chaîne recherchée est présente dans la moitié ou plus des enregistrements.

La fonction en elle-même ne permet pas de filtrer les résultats, elle indique uniquement un degré de pertinence. Tous les enregistrements sont donc renvoyés. Puisque l'absence de la chaîne est signalée par un 0, l'on peut donc effectuer un filtrage grâce à cette même fonction.

SELECT   titre, MATCH (texte, commentaire, titre) AGAINST ( 'test' )
FROM  example_fulltext
WHERE MATCH (texte, commentaire, titre) AGAINST ( 'test' )

La valeur 0 étant évaluée à false, les tuples évalués à 0 ne sont pas renvoyés.

À partir de la version 4.0.1, il est possible d'aller plus loin dans les recherches textuelles en utilisant la méthode booléenne. La recherche n'est alors plus sensible à la casse. Il est aussi possible d'effectuer une recherche dans les colonnes de type BLOB et d'utiliser des opérateurs booléens.

Télécharger le fichier fulltext.sql.

CREATE TABLE example_fulltext(
    idexample_fulltext INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    date_texte DATE NOT NULL,
    titre VARCHAR(200) NULL,
    auteur INTEGER NOT NULL,
    commentaire TINYTEXT NULL,
    texte TEXT NULL,
    validite BOOL NULL,
    PRIMARY KEY(idexample_fulltext),
    FULLTEXT INDEX example_fulltext_plein(texte, commentaire, titre),
    INDEX example_fulltext_auteur(auteur)
);

CREATE TABLE example_auteur(
    idexample_auteur INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    nomauteur varchar(50)
);

Nous allons prendre un exemple simple, deux tables. La première, example_fulltext, contient des extraits de textes de grands auteurs, la seconde, example_auteur, contient les noms des auteurs en question.

Commençons par une requête simple recherchant le mot « mort » dans l'ensemble des textes :

SELECT titre, MATCH (texte, commentaire, titre) AGAINST ('mort') AS  cpt
FROM example_fulltext
ORDER BY cpt DESC

Ajoutons un filtre permettant de n'extraire que les enregistrements comportant le mot « mort » :

SELECT titre, MATCH (texte, commentaire, titre) AGAINST ('mort') AS  cpt
FROM example_fulltext
WHERE MATCH (texte, commentaire, titre) AGAINST ('mort')
ORDER BY cpt DESC

L'on peut ajouter les auteurs avec une jointure.

SELECT titre, MATCH (texte, commentaire, titre) AGAINST ('mort') AS  cpt,Nomauteur
FROM example_fulltext  
inner join example_auteur on auteur  =  idexample_auteur
WHERE MATCH (texte, commentaire, titre) AGAINST ('mort')
ORDER BY cpt DESC

Maintenant, supposant que l'on veuille savoir quels sont les textes où se trouve le mot « mort » associé au mot « peuple » ?

Nous avons besoin d'utiliser une requête booléenne. Le mode booléen a été introduit avec la version 4.0.1 de MySQL.

SELECT titre, MATCH (texte, commentaire, titre) AGAINST ('mort peuple' IN BOOLEAN MODE) AS  cpt
FROM example_fulltext  inner join example_auteur on auteur  =  idexample_auteur
WHERE MATCH (texte, commentaire, titre) AGAINST ('mort peuple' IN BOOLEAN MODE)
ORDER BY cpt DESC

Si nous voulons donner plus de poids au mot « mort », alors il faut utiliser '+mort peuple'.

Essayons d'autre part de trouver les mots se rapportant au mot « république » (républicain, républicanisme…). Il faut alors utiliser l'opérateur *.

SELECT titre, Nomauteur
FROM full.example_fulltext  inner join example_auteur on auteur  =  idexample_auteur
WHERE MATCH (texte, commentaire, titre) AGAINST ('répu*' IN BOOLEAN MODE)

Nous tenons à remercier f-leb pour sa relecture orthographique et Malick SECK pour la mise au gabarit.