7.2.4 Comment MySQL optimise les clauses WHERE

Les optimisation de la clause WHERE sont présentées avec la commande SELECT car elles sont généralement utilisées avec la commande SELECT , mais les mêmes optimisations peuvent s'appliquer aux clauses WHERE des commandes DELETE et UPDATE .

Certaines des optimisations effectuées par MYSQL sont présentées ici :

• Suppression 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 constantes :

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

• Suppression des conditions constantes (nécessaires pour le 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 sont évaluées une fois.
• COUNT(*) sur une table simple, sans clause WHERE est lu directement dans les informations de la table pour les tables MyISAM et HEAP . Cela peut aussi être fait avec les expressions NOT NULL lorsqu'elles sont utilisées sur une seule table. table.
• Détection précoce est expressions constantes invalides. MySQL détecte rapidement les commandes SELECT qui sont impossibles, et ne retourne aucune ligne.
• HAVING est combiné avec la clause WHERE si vous n'utilisez pas la clause GROUP BY ou les fonctions de groupe ( COUNT() , MIN() ...).
• Pour chaque sous-jointure, une clause WHERE simplifiée est construite pour accélérer l'évaluation de WHERE pour chaque sous-jointure, et aussi essayer d'ignorer les lignes le plus tôt possible.
• Toutes les tables constantes sont lues en premier, avant toute autre table de la requête. Une table constante est une table :
  • Une table vide ou une table d'une ligne.
  • Une table qui est utilisée avec la clause WHERE sur un index de type UNIQUE , ou avec une clé primaire PRIMARY KEY , dont toutes les parties sont des expressions constantes, et les parties de l'index sont identifiées comme NOT NULL .
  Toutes les tables suivantes sont considérées comme 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 de jointure est obtenue en testant toutes les possibilités. Si toutes les colonnes des clauses ORDER BY et GROUP BY proviennent de la même table, cette table sera utilisée de préférence comme première table dans la jointure.
• Si il y a une clause ORDER BY et une clause GROUP BY différente, ou si la clause ORDER BY ou GROUP BY contient des colonnes issues des tables autres que la première, une table temporaire est créée.
• Si vous utilisez SQL_SMALL_RESULT , MySQL va utiliser une table temporaire en mémoire.
• Chaque index de table est interrogé, et le meilleur index qui représente moins de 30% des lignes est utilisé. Si un tel index ne peut être identifié, un scan rapide de la table est fait.
• Dans certains cas, MySQL peut lire des lignes depuis l'index sans même consulter le fichier de données. Si toutes les colonnes de l'index sont des nombres, alors seul l'arbre d'index sera utilisé pour résoudre la requête.
• Avant chaque affichage de ligne, celles qui ne satisfont pas les critères de la clause HAVING sont ignorées.

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

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

mysql> SELECT ... FROM tbl_name
    ->            ORDER BY key_part1,key_part2,... ;
mysql> SELECT ... FROM tbl_name
    ->            ORDER BY key_part1 DESC,key_part2 DESC,... ;