10.5 Comment MySQL optimise les clauses WHERE

(Cette section est incomplète, car MySQL effectue de nombreuses optimisations.)

En général, lorsque vous voulez accélérer le traitement d'une commande SELECT ... WHERE, le premier reflexe a avoir est de regarder si il n'est pas possible d'ajouter un index. Toutes les références entre tables devraient utiliser des index. Vous pouvez utiliser la commande EXPLAIN déterminer quels index sont utilisés au cours d'une commande SELECT. EXPLAIN.

Une partie des optimisations de MySQL sont présentées ci-dessous :

• Elimination des parenthèses inutiles

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

• Remplacement des variables par des constantes

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

• Elimination des conditions constantes (à cause du remplacement des constantes)

   (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

• Les expressions constantes utilisées par les index ne sont évaluées qu'une seule fois.
• COUNT(*) exécuté sur une seule table sans clause WHERE est directement lu dans les informations de la table. C'est aussi vrai pour les expression NOT NULL utilisé sur une seule table.
• Détection précoce des expressions constantes invalides. MySQL détecte que certaines commandes SELECT sont impossibles et retourne aucune ligne.
• HAVING est fusionné avec WHERE si vous n'utilisez pas GROUP BY ou des fonctions de groupage, telles que COUNT(), MIN()...
• Pour chaque sous-regroupement, une requête WHERE plus simple est constituée, pour accélérer l'exécution et abandonner les requêtes les plus tôt possible.
• Toutes les tables constantes sont lues en premier, avant toute autre tables dans la requête. Une table constante est:
  • Une table vide ou avec une seule ligne
  • Une table qui est utilisé avec une clause WHERE avec un index UNIQUE ou une PRIMARY KEY, et si toutes les membres de l'index sont utilisé avec des expressions constantes.

Toutes les tables suivantes sont utilisées comme des tables constantes :

mysql> SELECT * FROM t WHERE primary_key=1;
mysql> SELECT * FROM t1,t2
           WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

• La meilleure combinaison pour un regroupement est réalisée en essayant toutes les possibilités (si toutes les colonnes de la clause ORDER BY et GROUP BY proviennent de la même table, alors cette table est traitée en premier, lors du regroupement) .
• Si il y a une clause ORDER BY et une clause GROUP BY différente, ou si ORDER BY ou GROUP BY contiennent des colonnes d'autres tables que la première table dans la commande de regroupement, une table temporaire est créée.
• Si vous utilisez SQL_SMALL_RESULT, MySQL utilisera une table temporaire en mémoire.
• Puisque DISTINCT est convertie en GROUP BY sur toutes les colonnes, DISTINCT combiné avec ORDER BY nécessitera la plus part du temps une table temporaire.
• Les index de table sont d'autant plus rapide et efficace qu'ils ne prennent en compte que 30% de la longueur de la ligne. Si aucun index n'est trouvé, la table est rapidement analysée.
• Dans certains cas, MySQL peut lire des lignes sans même consulter le fichier de données. Si toutes les colonnes utilisées pour l'index sont numériques, alors seul l'arbre d'index sera utilisé pour résoudre la requête.
• Avant d'être retourné, toute ligne qui ne satisfait par le critère de HAVING sont ignorés.

Quelques exemples de requête très rapides :

mysql> SELECT COUNT(*) FROM nom_table;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM nom_table;
mysql> SELECT MAX(key_part2) FROM nom_table
           WHERE key_part_1=constant;
mysql> SELECT ... FROM nom_table
           ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM nom_table
           ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

Les requête suivantes sont résolues en utilisant uniquement l'arbre d'index (on supposera que toutes les colonnes indexées sont numériques)

mysql> SELECT key_part1,key_part2 FROM nom_table WHERE key_part1=val;
mysql> SELECT COUNT(*) FROM nom_table
           WHERE key_part1=val1 AND key_part2=val2;
mysql> SELECT key_part2 FROM nom_table GROUP BY key_part1;

Les requêtes suivantes sont indexées pour retourner les lignes classées, sans nécessiter de clause de classement :

mysql> SELECT ... FROM nom_table ORDER BY key_part1,key_part2,...
mysql> SELECT ... FROM nom_table ORDER BY key_part1 DESC,key_part2 DESC,...