Constat : les FBI (function-based index) ne sont pas disponibles en MySQL.
Comment faire dans ce cas pour obtenir par exemple l’équivalent de l’index suivant ?
CREATE INDEX idx_str_len ON my_table (length(str));
Une des solutions les plus simples consiste à utiliser un trigger qui assurera la mise à jour d’une colonne supplémentaire que l’on créera dans la table cible.
Partons de la table suivante :
CREATE TABLE `t` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`date` timestamp NOT NULL,
`str` varchar(100) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
Le but est de pouvoir obtenir une réponse rapide à la requête suivante :
select sql_no_cache count(*) from t where length(str) between 20 and 70;
Afin de simuler un jeu d’essai similaire à ma table de production, j’alimente ma table de test grâce à une procédure stockée déjà évoquée sur dbnewz.
Je la modifie ici afin d’obtenir une longueur aléatoire pour ma chaîne de caractère « str » :
delimiter //
CREATE PROCEDURE fill_table(nb_rows INT)
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SET i = i + 1;
INSERT INTO t (str) VALUES(repeat('a', round(rand()*100)));
UNTIL i >= nb_rows
END REPEAT;
END;
//
delimiter ;
Afin de valider deux scénarios pour ce billet, j’ai utilisé deux machines, une machine perso et un serveur pro. Deux configurations différentes : la perso n’est absolument pas « tunée », en configuration strictement d’origine (5.1.32), la seconde a des processeurs et de la RAM à revendre.
J’afficherai donc les temps d’exécution des deux machines à titre de comparaison.
)