10.4 Utilisation des index

Tous les index (PRIMARY, UNIQUE et INDEX()) sont stockés dans des B-trees. Les chaînes sont automatiquement compressées : CREATE INDEX.

Les index sont utilisés pour :

• Rechercher rapidement une ligne qui vérifie une clause WHERE.
• Retrouver des lignes d'une autre table lors d'un regroupement.
• Trouver la valeur MAX() ou MIN() d'une clé donnée
• Trier ou regrouper les éléments d'une table, si le tri ou le regroupement est fait avec le préfixe d'une clé accessible (e.g. ORDER BY key_part_1,key_part_2 ). La clé sont lus dans l'autre sens si on utilise l'attribue DESC.
• Retourner des valeurs sans consutler le fichier de données, dans certains cas. Si toutes les colonnes d'une table sont des numériques, et forment la partie gauche du préfixe d'une clé, ces valeurs peuvent être retournée directement depuis un index, pour plus de rapidité.

Supposons que vous voulez exécuter la commande SELECT suivante :

mysql> SELECT * FROM nom_table WHERE col1=val1 AND col2=val2;

Si un index multi-colonne existe avec col1 et col2, les lignes adéquates peuvent être retrouvée directement. Si des index existe sur les colonnes col1 et col2, l'optimiseur décide quel index va retourner le moins de lignes, et utilise cet index pour retourner les lignes.

Si la table a des index multi-colonnes, tous les prefixes à gauches de l'index peut être utilisé par l'optimiseur pour retrouver les lignes. Par exemple, si vous avez un index de trois colonnes (col1,col2,col3), vous avez la possibilité de rechercher rapidement sur les colonnes (col1), (col1,col2) et (col1,col2,col3).

MySQL ne peut pas utiliser un index partiel si les colonnes ne forment pas la partie gauche d'un préfixe d'index. Supposons que vous ayez la commande suivante :

mysql> SELECT * FROM nom_table WHERE col1=val1;
mysql> SELECT * FROM nom_table WHERE col2=val2;
mysql> SELECT * FROM nom_table WHERE col2=val2 AND col3=val3;

Si un index existe sur les colonnes (col1,col2,col3), alors seule la première requête utilisera un index. Les deux autres requête font appel à des colonnes indexées, mais (col2) et (col2,col3) ne sont pas les préfixes à gauche de (col1,col2,col3).

MySQL utilise aussi des index pour des comparaisons de type LIKE si l'argument de LIKE est une chaîne constante qui ne commence pas par un caractère spécial. Par exemple, la commande suivante SELECT utilise des index :

mysql> select * from nom_table where key_col LIKE "Patrick%";
mysql> select * from nom_table where key_col LIKE "Pat%_ck%";

Dans la première commande, seule les lignes "Patrick" <= key_col < "Patricl" sont prises en considération. Dans la deuxième commande, seule les lignes avec "Pat" <= key_col < "Pau" sont prises en considération.

Les commandes suivantes ne vont pas utiliser d'index :

mysql> select * from nom_table where key_col LIKE "%Patrick%";
mysql> select * from nom_table where key_col LIKE other_col;

Dans la première, la clause LIKE commence par un caractère spécial. Dans la deuxième, la clause LIKE n'est pas constante.

Effectuer des recherches en utilisant column_name IS NULL utilisera des index si column_name est un index.

MySQL utilise prioritairement des index qui vont trouver le minimum de ligne. Un index est utilisé lors des comparaisons qui impliquent une colonne et un opérateur =, >, >=, <, <=, BETWEEN ou LIKE sans caractère spécial comme 'quelquechose%'.

Un index qui ne comprend pas toutes les colonnes utilisées dans une clause WHERE avec un opérateur AND n'est pas utilisé pour optimiser la requête.

Les requêtes suivantes utilisent des index :

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimized like "index_part1='hello'" */

Les requêtes suivantes n'utilisent pas d'index :

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 is not used */
... WHERE index=1 OR A=10                  /* No index */
... WHERE index_part1=1 OR index_part2=10  /* No index spans all rows */