Les index sont utilisés pour trouver des lignes de résultat avec une valeur spécifique, très rapidement. Sans index, MySQL doit lire successivement toutes les lignes, et à chaque fois, faire les comparaisons nécessaires pour extraire un résultat pertinent. Plus la table est grosse, plus c'est coûteux. Si la table dispose d'un index pour les colonnes utilisées, MySQL peut alors trouver rapidement les positions des lignes dans le fichier de données, sans avoir à fouiller toute la table. Si une table à 1000 lignes, l'opération sera alors 100 fois plus rapide qu'une lecture séquentielle. Notez que si vous devez lire la presque totalité des 1000 lignes, la lecture séquentielle se révélera alors plus rapide, malgré tout.

Tous les index de MySQL (PRIMARY, UNIQUE et INDEX) sont stockés sous la forme de B-tree. Les chaînes sont automatiquement préfixée et leurs espaces terminaux sont supprimés. See Section 13.2.4, « Syntaxe de CREATE INDEX ».

Les index sont utilisés pour :

Supposez que vous utilisiez la commande SELECT suivante :

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

Si un index multi-colonne existe sur les colonnes col1 et col2, les lignes appropriées seront directement lues. Si des index séparés sur les colonnes col1 et col2 existent, l'optimiseur va essayer de trouver l'index le plus restrictif des deux, en décidant quel index débouche sur le moins de lignes possibles.

Si une table a un index multi-colonne, tout préfixe d'index peut être utilisé par l'optimiseur pour trouver des lignes. Par exemple, si vous avez un index à trois colonnes (col1,col2,col3), vous pouvez faire des recherches accélérées sur les combinaisons de colonnes (col1), (col1,col2) et (col1,col2,col3).

MySQL ne peut utiliser d'index partiel sir les colonnes ne forment pas un préfixe d'index. Supposez que vous avez la commande SELECT suivante :

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

Si un index existe sur les colonnes (col1,col2,col3), seule la première requête pourra utiliser l'index ci-dessus. Les deux autres requêtes utilisent des colonnes indexées, mais les colonnes (col2) et (col2,col3) ne font pas partie du préfixe des colonnes (col1,col2,col3).

MySQL utilise aussi les index lors des comparaisons avec l'opérateur LIKE si l'argument de LIKE est une chaîne constante qui ne commence pas par un caractère joker. Par exemple, les requêtes SELECT suivantes utilisent des index :

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE "Pat%_ck%";

Dans le premier exemple, seules les lignes avec "Patrick" <= key_col < "Patricl" sont considérées. Dans le second exemple, "Pat" <= key_col < "Pau" sont considérées.

Les commandes SELECT suivantes n'utilisent pas d'index :

mysql> SELECT * FROM tbl_name WHERE key_col LIKE "%Patrick%";
mysql> SELECT * FROM tbl_name WHERE key_col LIKE other_col;

Dans la première requête, la valeur associée à LIKE commence avec un caractère joker. Dans le second exemple, la valeur associée à LIKE n'est pas une valeur constante.

MySQL 4.0 fait une autre optimisation avec l'opérateur LIKE. Si vous utilisez ... LIKE "%string%" et que string est plus grand que 3 caractères, MySQL va utiliser l'algorithme Turbo Boyer-Moore qui prend une valeur initiale pour résoudre le masque, et l'exploite pour accélérer la recherche.

Les recherches qui utilisent la fonction column_name IS NULL vont utiliser les index si column_name sont des index.

MySQL normalement utilise l'index qui génère le moins de lignes possible. Un index est utilisé avec les colonnes que vous spécifiez, et les opérateurs suivants : =, >, >=, <, <=, BETWEEN et l'opérateur LIKE sans préfixe joker, c'est à dire de la forme 'quelquechose%'.

Un index qui ne s'applique pas à tous les niveaux de AND dans une requête WHERE, ne sera pas utilisé pour optimiser la requête. En d'autres termes, pour être capable d'utiliser un index pour optimiser une requête, un préfixe de l'index doit être utilisé dans toutes les parties de la formule logique contenant AND.

Les clauses WHERE suivantes utilisent des index :

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
... WHERE index=1 OR A=10 AND index=2      /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
          /* optimisé par "index_part1='hello'" */
... WHERE index1=1 and index2=2 or index1=3 and index3=3;
          /* peut utiliser un index sur index1 mais pas sur index2 ou index 3 */

Ces clauses WHERE n'utilisent pas d'index :

... WHERE index_part2=1 AND index_part3=2  /* index_part_1 n'est pas utilisé */
... WHERE index=1 OR A=10                  /* Index n'est pas utilisé sur les deux parties du AND */
... WHERE index_part1=1 OR index_part2=10  /* Aucun index ne s'applique à toutes les colonnes */

Notez que dans certains cas, MySQL ne va pas utiliser un index, même s'il y en a un disponible. Si l'utilisation de l'index requiert que MySQL accède à plus de 30% des lignes de la table (dans ce cas, un scan de table est probablement plus rapide, et demandera moins d'accès disques). Notez que si une telle requête utilise la clause LIMIT pour ne lire qu'une partie des lignes, MySQL utilisera tout de même l'index, car il va trouver plus rapidement les quelques lignes de résultat.

Les index hash ont des caractéristiques différentes de celles présentées :