Créer un moteur de recherche avancé avec les index Fulltext de MySQL

Créer un moteur de recherche pertinent pour un site internet n'est pas une chose facile. En général le premier réflexe c'est d'utiliser des requêtes SQL avec des LIKE pour rechercher les mots clés de la requête dans différents champs d'une table.

Et bien il existe une bien meilleure solution : les index Fulltext.

Un index fulltext peut être ajouté à une ou plusieurs colonnes dans une table, et il permet de pouvoir rechercher des mots clés très rapidement dans ces colonnes, sans avoir à gérer soit même les conditions dans la requête SQL.

Comment créer un index fulltext ?

Rien de plus facile, un simple ALTER TABLE permet d'ajouter un index fulltext sur une ou plusieurs colonnes.

Voici un exemple :

ALTER TABLE `faq` ADD FULLTEXT INDEX `search` (`permalien`, `titre`, `title`);

faq correspond au nom de la table à laquelle vous voulez ajouter l'index. search c'est tout simplement le nom de l'index (c'est comme ça, il faut lui donner un nom unique qui l'identifie). Et enfin, permalien, titre et title sont les noms des champs qui seront indexés.

Tous les champs qui composent un index doivent avoir le même jeu de caractères (charset) et le même interclassement (collation). Lors de la création d'un index, vous allez peut-être rencontrer cette erreur SQL :

Column 'foobar' cannot be part of FULLTEXT index

Cette erreur survient généralement lorsque cette règle n'est pas respectée. Pour résoudre le problème, il faut changer le charset et l'interclassement des colonnes concernées pour que toutes les colonnes soient identiques.

Seuls les champs de types CHAR, VARCHAR et TEXT peuvent être utilisés pour construire un index fulltext.

Utiliser un index fulltext

Pour rechercher avec un index fulltext, il existe une syntaxe spécifique qui utilise l'expression MATCH AGAINST :

SELECT *
FROM faq
WHERE MATCH(permalien, titre) AGAINST ('tutoriel mysql');

Cette requête va chercher les tuples qui contiennent les mots "tutoriel" ou "mysql" dans les colonnes permalien et titre.

Mais ça n'est pas tout, on peut aussi imposer la présence (avec +) ou l'absence (avec -) de certains mots gràce à un mode spécial qu'on appelle le BOOLEAN MODE.

Voici un billet similaire qui vous permettera d'en savoir plus sur forcer le format d'une cellule excel.

Dans ce mode, l'expression recherchée va être interprétée avant le traitement, pour prendre en charge certains métacaractères comme + et -.

Dans l'exemple ci-dessous, on cherche les tuples qui contiennent les mots "tutoriel" et "mysql" et qui ne contiennent pas le mot "wordpress" :

SELECT *
FROM faq
WHERE MATCH(permalien, titre) AGAINST ('+tutoriel +mysql -wordpress' IN BOOLEAN MODE);

Comment marche un index fulltext ?

Un index full texte (que l'on nomme aussi parfois "catalogue fulltext") n'est en réalité qu'une liste de mots, chaque mot étant associé à une liste d'idenfitiants de tuples qui les contiennent (on appelle cela une hashtable).

La recherche dans une telle structure de données est beaucoup plus rapide que de chercher dans tous les champs du contenu.

De plus, les index fulltext sont intelligents, ils n'indexent pas tous les mots. En effet, certains mots comme les articles, les prépositions... (que l'on appelle des stopwords) n'ont pas d'intérêt sémantique et ne seront donc pas ajoutés dans l'index.

Pas d'index fulltext pour InnoDB

Ah j'ai oublié de vous prévenir, seules les tables utilisant le moteur MyISAM peuvent être dotées d'index fulltext. Les tables InnoDB ne sont pas compatibles avec cette fonctionnalité. C'est d'ailleurs un critère important pour choisir entre InnoDB et MyISAM.

Encore faim ? allez lire ça : dessiner un dragon !