Avant de poser un index sur un (ou plusieurs) champs d’une table, il faut évaluer de la manière la plus précise possible l’impact qu’aura cette pose sur le temps d’exécution des requêtes. Lorsque les développeurs découvrent les index (j’en connais qui sont en poste depuis des années et ne savent même pas ce que c’est…), ils ont tendance à en mettre de partout. Gare ! Un index posé à tort peut pénaliser les sélections, les mises à jour/suppressions/insertions. Regardons un exemple très simple; Henri a décidé que puisque les index sont là pour accélérer les recherches, il va en poser un sur sa table, dont voici le schéma (l’intention) :

create table if not exists test_index
(
   id mediumint unsigned not null primary key,
   nom char(4) not null,
   valid tinyint unsigned not null
);

Sa table comporte 100 000 tuples, qu’il insère avec une procédure stockée :

DROP PROCEDURE IF EXISTS insertion;

DELIMITER //
CREATE PROCEDURE insertion()
BEGIN
    DECLARE i INT DEFAULT 1;

    WHILE (i<=100000) DO
        INSERT INTO test_index VALUES(i,'test', 1);
        SET i=i+1;
    END WHILE;
END
//
CALL insertion();

Il part du principe (plutôt intelligent, au demeurant) que la requête qu’il fait le plus souvent étant :

select * from test_index where valid = 1;

Il serait judicieux qu’il pose un index sur valid :

alter table test_index add index(valid);

Voilà la pose réalisée ! Une fois l’index en place, il a quand même le réflexe de valider que la pose de cet index accélère bien les recherches, comme il le souhaitait…

Il a bien ses 100 000 enregistrements…

mysql> select count(*) from test_index;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+

Avant la pose de l’index, un EXPLAIN sur sa requête favorite donnait :

On voit au passage qu’il réalisait un full table scan (type = ALL, possible_keys = NULL), c’est à dire qu’il parcourait l’ensemble des tuples de sa table (rows). Dorénavant, cela donne :

Henri constate avec satisfaction que la pose de son index lui fait lire moitié moins d’enregistrements (regardez la colonne rows). Pourtant, lorsqu’il lance sa requête, Henri est très déçu :

100000 rows in set (0.19 sec)

Alors que lorsqu’il détruit l’index, il obtient :

100000 rows in set (0.10 sec)

Pour résumer, Henri lit moitié moins de tuples…en deux fois plus de temps ! Ce n’est pas du tout conforme à l’idée qu’il se faisait des index et ses espoirs sont déçus ! En posant un index inutile sur un champ qui n’a qu’une valeur possible (1), Henri a posé un index qui pénalise le temps d’exécution de sa requête…Il aura tout de même compris que le seul vrai moyen de vérifier qu’on tire des bénéfices d’un index est le benchmarking des requêtes qui l’utilisent.