7.2.10 Index multi-colonnes

MySQL peut créer des indexes sur plusieurs colonnes en même temps. Un index peut contenir jusqu'à 15 colonnes (Avec les colonnes de type CHAR t VARCHAR , il est aussi possible d'utiliser un préfixe lors de l'indexation).

Un index de plusieurs colonnes peut être considéré comme un tableau trié contenant les lignes obtenues en concaténant les valeurs des colonnes indexées.

MySQL gère les index sur plusieurs colonnes de manière à ce que les requêtes qui recherche une valeur connue dans la première colonne (avec une clause WHERE), soient rapides, même si les valeurs pour les autres colonnes ne sont pas précisées.

Par exemple, soit la table suivante :

mysql> CREATE TABLE test (
           id INT NOT NULL,
           nom CHAR(30) NOT NULL,
           prenom CHAR(30) NOT NULL,
           PRIMARY KEY (id),
           INDEX nom_complet (nom, prenom));

Ainsi, l'index nom_complet est un index sur les deux colonnes nom et prénom. L'index sera utilisé lors des requêtes qui recherchent un nom, ou un nom et un prénom. L'index sera donc utilisé lors des requêtes suivantes :

mysql> SELECT * FROM test WHERE nom="Dupont";
mysql> SELECT * FROM test WHERE nom ="Dupont"
                          AND prenom="Michel";
mysql> SELECT * FROM test WHERE nom ="Dupont"
                          AND (prenom ="Michel" OR prenom ="Marie");
mysql> SELECT * FROM test WHERE nom ="Dupont"
                          AND prenom >="M" AND prenom < "N";

Cependant, l'index ne sera pas utilisé lors des requêtes suivantes :

mysql> SELECT * FROM test WHERE prenom ="Michel";
mysql> SELECT * FROM test WHERE nom="Dupont"
                          OR prenom ="Michel";