MySQL : Recherche et index FULLTEXT

how-to

Il est toujours sympa, quand on fait son site web, de proposer à ses visiteurs un petit champ qui leur permet de rechercher une news ou un article particulier, en saisissant des mots-clé. Voici donc quelques pistes pour réaliser un moteur de recherche simple mais efficace grâce à MySQL.

Pré-requis

Vous aurez besoin :

  • d'un serveur MySQL (version 4.0.21 minimum),
  • de bonnes connaissances en SQL,
  • PHP 5.1
  • d'un peu de patience, et d'environ 1H.

La fonctionnalité présentée repose sur MySQL. Aussi, même si certains exemples sont présentés avec PHP et HTML, vous pouvez l'appliquer à n'importe quel autre langage de programmation, tant que la base de données est gérée par MySQL.

Situation

Pour la suite, nous allons considérer la table suivante :

CREATE TABLE articles(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(255) NULL,
    article TEXT NULL,
    date DATETIME NULL,
    auteur VARCHAR(255) NULL
)
TYPE=MyISAM;

Notre moteur de recherche devra rechercher dans les colonnes titre et article de la table. Je vous laisse le soin d'adapter ça en fonction de votre schéma de base de données, ça ne devrait pas être très compliqué :)

Bien ! Maintenant que le décor est planté, let's code !

Première solution : l'utilisation de LIKE

La solution la plus évidente et la plus simple lorsqu'on désire rechercher un ou plusieurs mots dans un champs contenant du texte est d'utiliser le mot clé LIKE. Dans notre cas, cela donnerait :

SELECT * FROM articles WHERE article LIKE '%ma_recherche%' OR titre LIKE '%ma_recherche%';

Malheureusement cette solution a beaucoup de défauts :

  • Tout d'abord, elle va renvoyer un grand nombre de résultats. Ceux-ci ne seront donc pas forcément très pertinents. Ceci est d'autant plus vrai si l'utilisateur saisit des mots très communs qui se retrouveront dans chaque article ("de", "est", etc.).
  • Ensuite, cette méthode peut avoir des effets de bord indésirables. Admettons, par exemple, que certains de vos articles contiennent des liens dont l'URL se termine par ".php". Imaginez maintenant que l'utilisateur saisisse dans le champs de recherche le terme PHP... Tous les articles contenant des liens se terminant par ".php" vont ressortir ! Catastrophe :(

Deuxième solution : recherche en texte intégral

Heureusement, depuis sa version 3.23.23, MySQL propose l'indexation et la recherche sur l'ensemble d'un champ de type TEXT. Cette fonctionnalité a énormément d'avantages par rapport à LIKE puiqu'elle va nous permettre de ne récupérer que les enregistrements réellement pertinents.

Pour indexer le contenu des enregistrements, MySQL utilise un algorithme basé sur des principes simples :

  • MySQL ignore les mots trop courts, c'est à dire de longueur inférieure à 4 caractères.
  • De même, MySQL ignore les mots contenus dans une liste de mots interdits (myisam/ft_static.c). En français, cette liste peut contenir des mots comme "être", "avoir", etc. Ces mots sont jugés trop communs, et donc sans réelle pertinence lors d'une recherche.
  • MySQL donne ensuite un poids à chaque mot, en fonction du nombre de fois qu'il apparaît dans les enregistrements. Ainsi, un mot qui apparaît très souvent sera jugé moins pertinent et aura donc un poids plus faible qu'un terme rare.
  • En complément, MySQL ignore tous les termes présents dans plus de 50% des enregistrements. Cette limite est très importante : au delà, MySQL considère que ces mots n'ont pas un poids assez important pour être pertinents et les ignore.

Lorsque nous effectuons une recherche sur des colonnes indexées, MySQL additionne les poids de chaque mot contenu dans un enregistrement et en déduit une pertinence. Si la pertinence est égale à zéro, l'enregistrement est simplement ignoré.

1. Mise en place

Voyons maintenant comment mettre en place ce système. La première étape consiste à modifier un peu notre table en créant un index Fulltext. Cet index doit obligatoirement être créé sur l'ensemble des colonnes à indexer. Dans notre cas, la recherche porte sur les colonnes titre et article. Notre index sera donc créé sur ces deux colonnes.

Si votre table existe déjà, un petit :

ALTER TABLE articles ADD FULLTEXT mon_index (titre, article);

devrait suffire. La requête peut prendre un peu de temps car MySQL va indexer le contenu des champs pour les enregistrements existants. Soyez un peu patient :)

Si vous n'avez pas encore créé votre table, vous pouvez le faire ainsi :

CREATE TABLE articles(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    titre VARCHAR(255) NULL,
    article TEXT NULL,
    date DATETIME NULL,
    auteur VARCHAR(255) NULL,
    FULLTEXT mon_index (article, titre)
)
TYPE=MyISAM;

2. Utilisation

Maintenant que notre table indexe le contenu des colonnes titre et article, voyons comment rechercher. Bonne nouvelle, la requête SQL reste très simple :

SELECT *
FROM articles
WHERE MATCH (titre, article) AGAINST ('ma_recherche');

Et voilà ! En prime, MySQL trie automatiquement les résultats par ordre de pertinence décroissant.

Ah, oui, certains voudront peut-être afficher la pertinence de chaque résultat. Pour cela, il faut modifier un peu notre requête. Celle-ci devient :

SELECT *, MATCH (titre, article) AGAINST ('ma_recherche') AS pertinence
FROM articles
WHERE MATCH (titre, article) AGAINST ('ma_recherche');

3. Exemple complet en PHP

Voyons tout d'abord notre formulaire de recherche. Celui-ci est très simple : il est composé d'un champ de saisie et d'un bouton permettant de lancer la recherche :

<form method="get" action="search.php">
    <input type="text" name="q" value="" />
    <input type="submit" value="Rechercher" />
</form>

Le formulaire envoie les données saisies à la page search.php, écrivons celle-ci :

<?php
    // On commence par créer une connexion à notre base de données
    $dsn = "mysql:dbname=".$dbname.";host=".$host;
    $db = new PDO($dsn, $user, $passwd);

    // On crée la requête :
    $sql = "SELECT titre, article, date FROM articles";
    $sql .= " WHERE MATCH (titre, article) AGAINST (:query)";

    // On la prépare :
    $stmt = $db->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

    // Et on l'execute :
    $stmt->execute(array(":query" => $_GET["q"]));

    // On traite le résultat :
    foreach($stmt->fetchAll(PDO::FETCH_OBJ) as $r)
    {
        echo htmlentities($r->titre);
        echo htmlentities($r->article);
        echo htmlentities($r->date);
    }

    $stmt->closeCursor();
?>

Et voilà ! Bien entendu, cet exemple est très simple. Je vous laisse le soin de l'adapter à vos besoins et préférences :)

Pour aller plus loin : un moteur de recherche booléen.

Un moteur de recherche booléen comprend et gère les opérateurs booléens (+, -, >, <, etc...). Cette fonctionnalité peut-être très pratique dans le cas où l'utilisateur a besoin de pouvoir faire des recherches avec précision(s). Il va pouvoir, par exemple, rechercher : +mysql -html pour obtenir les articles qui contiennent mysql mais pas html.

Le fonctionnement est quasiment le même que pour les recherches simples. Il faut simplement garder à l'esprit que, dans ce mode :

  • Le seuil de 50% n'est pas pris en compte,
  • Les résultats ne sont pas triés par ordre de pertinence,
  • Il est indispensable de spécifier dans sa requête que la recherche se fait en boolean mode.

Pour cela, il suffit d'ajouter à sa requête les mots clé IN BOOLEAN MODE. Notre requête devient donc :

SELECT * FROM articles
WHERE MATCH (titre, article)
AGAINST ('+ma_recherche -je_ne_veux_pas_ça' IN BOOLEAN MODE);

Sympa, non ?

Pour creuser le sujet...