Optimisation des requêtes MySQL LIKE '%string%' dans InnoDB

De nombreuses applications nécessitent la recherche d'une chaîne dans un corps de texte. MySQL propose un opérateur LIKE pour effectuer de telles recherches, mais comment pouvons-nous optimiser ces requêtes pour des performances optimales ?

Problème

Considérez le tableau suivant :

CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Lors de l'exécution d'une requête telle que :

SELECT id FROM example WHERE keywords LIKE '%whatever%'

nous pouvons nous attendre à ce que la base de données utilisez un index pour accélérer la recherche. Cependant, si nous ajoutons un index simple sur la colonne des mots-clés en utilisant :

ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);

une requête EXPLAIN révèle que MySQL doit encore analyser la table entière.

Solution

MySQL utilise des index en faisant correspondre le début de la chaîne de recherche. Pour les requêtes telles que LIKE 'whatever%', l'index peut être utilisé car 'whatever' est ancré au début de la chaîne.

Cependant, les requêtes comme LIKE '%whatever%' n'ont pas une telle ancre. Le terme de recherche apparaît "flottant" dans la chaîne, obligeant MySQL à analyser l'intégralité du champ.

Pour optimiser ces requêtes, nous pouvons utiliser des index de texte intégral. Ces index sont spécifiquement conçus pour les recherches « flottantes ». InnoDB prend en charge les index de texte intégral depuis la version 5.6.4, ce qui en fait une option viable pour optimiser les requêtes LIKE '%string%'.