Indexation full-text et recherche sur index full-text - Mysql

On en apprend tous les jours ...

Certain vont dire que c'est une des base du langage SQL d'autre y verrons une révolution, peu importe je vais partager avec vous ma nouvelle découverte (je remercie au passage Steuf de ExInsidePhp).

Je vais traiter dans cet article l’intérêt et l’exploitation de l'index Full-Text de mysql.

1- Créer un index full-Text

Brièvement un index dans une table Mysql permet d’accélérer l’accès aux données lorsque ce champs est donnée dans la clause "WHERE" de votre requête.

Généralement on index les champs non textuelle comme les date, datetime ou int ..

Mais il existe un index textuelle pour accélérer la recherche parmi des champs en CHAR, VARCHAR, ou TEXT .

Pour créer votre index sur une seule colonne:

ALTER table NOM_DE_LA_TABLE ADD fulltext NOM_DE_VOTRE_INDEX (NOM_DU_CHAMP);

Pour créer un index sur deux ou plus :

ALTER table NOM_DE_LA_TABLE ADD fulltext NOM_DE_VOTRE_INDEX (NOM_DU_CHAMP1 , NOM_DU_CHAMP2);

2 - Recherche sur un index full-text

Une fois les index correctement crées vous allez devoir oublié les commandes LIKE , LIKE '%%' qui n'exploitent pas du tout les index, vous aurez donc soit crée des index inutilement, soit augmenter considérablement le temps de recherche.

La commande qui a donc révolutionné ma vie accéléré ma requête est :

SELECT colone1, colone2 FROM ma_table WHERE MATCH (colone1, colone2) AGAINST ('mot clé');

Noté que ici on utilise Un seul index de 2 colonnes, nous avons donc juste avant creer un index sur 2 champ ! ( ALTER table NOM_DE_LA_TABLE ADD fulltext NOM_DE_VOTRE_INDEX (NOM_DU_CHAMP1 , NOM_DU_CHAMP2); )

Donc au lieu du traditionnelle :

Where colone1 LIKE '%mot clé%' OR colone2 LIKE '%mot clé%'

on aura :

Where MATCH (colone1, colone2) AGAINST ('mot clé');

Selon ma propre analyse, sur une table de 1 million d'entrée , la commande LIKE met environ entre 2.5 sec et 3 sec a renvoyer les résultats alors que MATCH met environ 0.2 sec !!

L'utilisation de MATCH est assez facile mais le résultats de ces requête peut être très particulier, en effet  les mots de moins de 4 lettres sont ignoré ( sauf configuration contraire), les expression qui sont contenue dans plus de 50% des lignes aussi seront ignorées puisque MATCH les considères comme moins pertinentes.

Notez aussi que MATCH renvoie les résultats par ordre de pertinence et non par ordre logique.

Je vous invite a lire la documentation très claire (pour peu qu'on prenne le temps de la lire (private joke @steuf) de la recherche en Full-text :

http://dev.mysql.com/doc/refman/5.0/fr/fulltext-search.html

Les dernieres videos de buzz

Voir les dernieres videos