Index FULLTEXT

Le mot clé FULLTEXT permet de créer un index de recherche intégrale. Les index de texte intégrale sont optimisés pour les recherches de mots ou d'expressions en langage courant. Il est par exemple, très pratique d'utiliser ce type d'index pour créer des moteurs de recherche pour sites Web. Les articles de votre site étant, dans ce cas, dans une base MySQL.

Attention, une requête de recherche ne marche que sur 4 lettres minimum et nécessite de nombreux enregistrements pour fonctionner. La recherche est insensible à la casse.

CREATE TABLE clients(
idclient tinyint PRIMARY KEY,
adresse VARCHAR(50),
...
FULLTEXT (adresse, nom)
);

CREATE FULLTEXT INDEX iadresse ON clients(adresse);

Pour effectuer une requête avec un tel index, nous utilisons le mot clé MATCH au lieu de LIKE. Le mot clé AGAINST() permet de préciser le ou les mots recherchés.

Syntaxe:
MATCH('nom_colonne') AGAINST ('mot_cle') AS 'valeur'

SELECT nom, prenom
FROM clients
WHERE MATCH(adresse) AGAINST('loin de toi')
LIMIT 0 , 30;

Recherche MATCH

Lorsque le mot clé MATCH est utilisé avec la clause WHERE ou la clause ORDER BY, les enregistrements sont retournés dans un ordre de pertinence, le premier étant le plus pertinent. Il est possible de connaître l'indice de pertinence avec une requête comme celle-ci:

SELECT nom,  prenom, MATCH(adresse) AGAINST('loin de toi') AS Pertinence
FROM clients
ORDER BY Pertinence DESC
LIMIT 0 , 30;

Si un mot apparaît dans de trops de nombreux enregistrements, il est ignoré car il pourrait fausser la pertinence. Par contre, le mot rare hérite d'une pertinence élevée.

SELECT nom,  prenom, MATCH(adresse) AGAINST('loin de toi') AS Pertinence
FROM clients
WHERE MATCH(adresse) AGAINST('loin de toi')
LIMIT 0 , 30;

Depuis la version 4 de MySQL, il est possible de faire des recherches en précisant des opérateurs booléens comme pour les moteurs de recherche.
Par exemple AGAINST('+mot1 +mot2' IN BOOLEAN MODE) cherchera les enregistrements où mot1 et mot2 apparaissent et AGAINST('+mot1 -mot2' IN BOOLEAN MODE) cherchera les enregistrements où mot1 apparaît mais sans mot2.

SELECT nom,  prenom, MATCH(adresse)
AGAINST('loin de toi' IN BOOLEAN MODE) AS Pertinence
FROM clients
ORDER BY Pertinence DESC
LIMIT 0 , 30;

SELECT nom,  prenom,
MATCH(adresse) AGAINST('+ loin -pont' IN BOOLEAN MODE) AS Pertinence
FROM clients
ORDER BY Pertinence DESC
LIMIT 0 , 30;

SELECT nom,  prenom,
MATCH(adresse) AGAINST('+"rue loin"' IN BOOLEAN MODE) AS Pertinence
FROM clients
ORDER BY Pertinence DESC
LIMIT 0 , 30;