MySQL et la recherche textuelle

Olivier Miossec

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.

Pour réagir à ce tutoriel, un espace de dialogue vous est proposé sur le forum : Commentez

- Titre : MySQL et la recherche textuelle

- Auteur : Olivier Miossec

- Parution : 30 avril 2003

- Mise à jour : 24 mars 2016

- Licence :

Sommaire

- I. Index

- II. Configuration

- III. Gestion des index

- IV. Méthode

- V. Études de cas

- VI. Conclusion et remerciements

I - Index

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.

II - Configuration

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.

ft_boolean_syntax

Opérateur autorisé pour une recherche en mode booléen.

ft_min_word_len

Taille minimum des mots indexés, par défaut 4 char.

ft_max_word_len

Taille maximum des mots indexés, par défaut 254 char.

ft_max_word_len_for_sort

Définit la taille maximum des mots à indexer lors des opérations de réparation d'index.

ft_stopword_file

Fichier listant les mots à ne pas indexer (articles, propositions…), à placer dans le répertoire de base de MySQL. Une chaîne vide permet de ne pas utiliser de liste, ce qui n'est pas forcément conseillé.

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 ».

III - Gestion des index

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.

Création d'un index « full text » dans l'instruction de création d'une table.

1. CREATE TABLE example_fulltext(

2. idexample_fulltext INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

3. date_texte DATE NOT NULL,

4. titre VARCHAR(200) NULL,

5. auteur VARCHAR(30) NULL,

6. commentaire TINYTEXT NULL,

7. texte TEXT NULL,

8. validite BOOL NULL,

9. PRIMARY KEY(idexample_fulltext),

10. FULLTEXT INDEX example_fulltext_plein(texte, commentaire, titre),

11. INDEX example_fulltext_auteur(auteur)

12. );

Création d'un index « full text » par altération de la table.

1. ALTER TABLE example_fulltext

2. 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.

IV - Méthode

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 :

1. MATCH ( colonnes ) AGAINST ( chaîne recherchée )

2. SELECT   titre, MATCH (texte, commentaire, titre) AGAINST ( 'test' )

3. FROM  example_fulltext

titre

MATCH (texte, commentaire, titre) AGAINST ( 'test' )

blogibulga

0

blogibulga

0

blogibulga

0

blogibulga

0

blogibulga

0

blogibulga

2.243120036181

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.

1. SELECT   titre, MATCH (texte, commentaire, titre) AGAINST ( 'test' )

2. FROM  example_fulltext

3. WHERE MATCH (texte, commentaire, titre) AGAINST ( 'test' )

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

titre

MATCH (texte, commentaire, titre) AGAINST ( 'test' )

blogibulga

02.243120036181

À 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.

Opérateur

Explication

Exemple

+

Le mot doit être présent dans toutes les lignes pour que la recherche soit valide.

+ mot_a mot_b
Permet d'obtenir un rang plus élevé pour les lignes contenant les deux mots.

-

Le mot n'a pas à être présent dans toutes les lignes pour que la recherche soit valide.

mot_a - mot_b
Permet d'obtenir un rang moins élevé pour les lignes contenant les deux mots.

*

Opérateur permettant d'omettre un nombre indéterminé de lettres sur un mot.

mot_a*
Recherche les mots commençant par mot_a.

< et >

Diminue/augmente le poids du mot dans la pertinence globale.

+mot_a >mot_b
Donne un rang plus élevé à mot_a mot_b.

~

Diminue la pertinence du mot dans la ligne sans exclusion.

mot_a ~ mot_b
Permet de marquer mot_b négativement sans l'exclure.

‘' ‘'

Permet d'introduire des caractères non alphanumériques ou de donner une phrase.

« grande chanson » permet d'obtenir la phrase « petit cheval » mais pas « grande et longue chanson ».

( )

Permet de définir un groupe d'expressions.

&

ET logique.

|

OU logique.

V - Études de cas

Télécharger le fichier fulltext.sql.

1. CREATE TABLE example_fulltext(

2. idexample_fulltext INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

3. date_texte DATE NOT NULL,

4. titre VARCHAR(200) NULL,

5. auteur INTEGER NOT NULL,

6. commentaire TINYTEXT NULL,

7. texte TEXT NULL,

8. validite BOOL NULL,

9. PRIMARY KEY(idexample_fulltext),

10. FULLTEXT INDEX example_fulltext_plein(texte, commentaire, titre),

11. INDEX example_fulltext_auteur(auteur)

12. );

14. CREATE TABLE example_auteur(

15. idexample_auteur INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

16. nomauteur varchar(50)

17. );

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 :

1. SELECT titre, MATCH (texte, commentaire, titre) AGAINST ('mort') AS  cpt

2. FROM example_fulltext

3. ORDER BY cpt DESC

titre

cpt

Lettre à Ménécée

1.3077735361583

De la Nature

1.2738213071353

Réfutation d'Helvétius

0.37034043359479

Essais

0.22855587241584

Déclaration des droits de l'homme et du citoyen

0

La Politique, livre I, chap. 2

0

La Politique, livre III, chap. 6 et 7

0

Principes de la philosophie

0

Le Prince

0

Le Prince

0

Traité du vide

0

La République

0

Du Contrat social

0

Du Contrat social

0

Traité sur la tolérance

0

Lettres à Lucilius

0

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

1. SELECT titre, MATCH (texte, commentaire, titre) AGAINST ('mort') AS  cpt

2. FROM example_fulltext

3. WHERE MATCH (texte, commentaire, titre) AGAINST ('mort')

4. ORDER BY cpt DESC

titre

cpt

Lettre à Ménécée

1.3077735361583

De la Nature

1.2738213071353

Réfutation d'Helvétius

0.37034043359479

Essais

0.22855587241584

L'on peut ajouter les auteurs avec une jointure.

1. SELECT titre, MATCH (texte, commentaire, titre) AGAINST ('mort') AS  cpt,Nomauteur

2. FROM example_fulltext

3. inner join example_auteur on auteur  =  idexample_auteur

4. WHERE MATCH (texte, commentaire, titre) AGAINST ('mort')

5. ORDER BY cpt DESC

titre

cpt

Nomauteur

Lettre à Ménécée

1.3077735361583

Épicure

De la Nature

1.2738213071353

HÉRACLITE

Réfutation d'Helvétius

0.37034043359479

DIDEROT

Essais

0.22855587241584

MONTAIGNE

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.

1. SELECT titre, MATCH (texte, commentaire, titre) AGAINST ('mort peuple' IN BOOLEAN MODE) AS  cpt

2. FROM example_fulltext  inner join example_auteur on auteur  =  idexample_auteur

3. WHERE MATCH (texte, commentaire, titre) AGAINST ('mort peuple' IN BOOLEAN MODE)

4. ORDER BY cpt DESC

Réfutation d'Helvétius

2

Lettre à Ménécée

1

De la Nature

1

Essais

1

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 *.

1. SELECT titre, Nomauteur

2. FROM full.example_fulltext  inner join example_auteur on auteur  =  idexample_auteur

3. WHERE MATCH (texte, commentaire, titre) AGAINST ('répu*' IN BOOLEAN MODE)

titre

Nomauteur

Du Contrat social

Jean-Jacques ROUSSEAU

La République

PLATON

Le Prince

Nicolas MACHIAVEL

Essais La Politique, livre III, chap. 6 et 7

ARISTOTE

Le Prince

Nicolas MACHIAVEL

VI - Conclusion et remerciements

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