7.4.5 Comment MySQL utilise les index

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.

• Trouver rapidement des lignes qui satisfont une clause WHERE .
• Ecarter rapidement des lignes. S'il y a un choix à faire entre plusieurs index, MySQL utilise généralement celui qui retourne le plus petit nombre de lignes.
• Lire des lignes dans d'autres tables lors des jointures.
• Trouver les valeurs MAX() et MIN() pour une colonne indexée. C'est une opération qui est optimisée par le preprocesseur, qui vérifie si vous utilisez la constante WHERE key_part_# = sur toute les parties de clés inférieures à < N. Dans ce cas, MySQL va faire une simple recherche de clé et remplacer l'expression par une constante. Si toutes les expressions sont remplacées par des constantes, la requête va alors être rapidement calculée :

  SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10

• Trier ou grouper des lignes dans une table, si le tri ou le regroupement est fait avec un préfixe à gauche utilisable (par exemple, ORDER BY key_part_1,key_part_2 ). La clé est lue en ordre inverse, si toutes les parties de clés sont suivies du mot clé DESC . Comment MySQL optimise ORDER BY .
• Dans certains cas, la requête peut être optimisée pour lire des valeurs sans consulter le fichier de données. Si cette possibilité est utilisée avec des colonnes qui sont toutes numériques, et forme le préfixe de gauche d'une clé, les valeurs peuvent être lues depuis l'index, à grande vitesse :

  SELECT key_part3 FROM table_name WHERE key_part1=1

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

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;

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%";

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

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

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 .

... 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 */

... 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 */

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

• Elles sont utilisées uniquement pour les comparaisons avec les opérateurs = ou <=> (mais elles sont très rapides).
• L'optimiseur ne peut pas utiliser un index hash pour accélérer une clause ORDER BY . Ce type d'index ne peut être utilisé que pour rechercher la prochaine ligne dans l'ordre.
• MySQL ne peut déterminer approximativement le nombre de lignes qui sont présentes entre deux valeurs : cette valeur est utilisée par l'optimiseur d'intervalle pour décider quel index utiliser. Cela affecte certaines requêtes, si vous changez la table MyISAM en table MEMORY .
• Seules les clés entières peuvent être recherchées, pour une ligne. Avec un index B-tree , un préfixe peut être utilisé pour trouver les lignes.