Nous allons voir comment et à quoi servent ANALYZE TABLE et OPTIMIZE TABLE, et dans un second temps, détailler le résultat que nous donne un EXPLAIN effectué sur une requête.

Optimiser ses tables

Deux commandes sont accessibles :

ANALYZE TABLE nomTable

Cette procédure demande au moteur de mettre à jour les calculs statistiques concernant les index, pour que l’optimiseur de requêtes soit au point dans le choix de ses méthodes ; choix que l’ont peut voir dans un EXPLAIN expliqué au chapitre suivant.

OPTIMIZE TABLE nomTable

Optimiser une table revient à la défragmenter, ce qui est très utile pour les tables de type DYNAMIC. Cela permet également de restructurer la table et retrier ses index (ce qui peut prendre un temps certain). On appellera cette procédure de façon périodique pour une table subissant de nombreuses mises à jour, de type DELETE, INSERT, UPDATE.

Optimiser des requêtes

Depuis la version 5.0.1, on peut utiliser conjointement la variable last_query_cost pour comparer le coût de la dernière requête effectuée.

EXPLAIN s’utilise très simplement, il suffit de le précéder au SELECT. La résultat nous donne plusieurs informations, dont quelques unes essentielles, et notamment le type de SELECT, qui nous donne l’information de la complexité du traitement. Voici différents types pouvant être renvoyés, classés du moins performant au plus rapide :

• ALL

La table entière est parcourue ligne par ligne. Très mauvais, il manque sûrement un index, ou la recherche se fait sur un sous ensemble du champ ne commençant pas par son début, par exemple un ... LIKE '%abc....

• INDEX

Tout comme ALL, mais on ne parcourt que l’index, pas la table.

• RANGE

La lecture se fait sur un sous-ensemble de la totalité des lignes de la table. Ceci n’est efficace que si l’unicité de l’index est élevé dans la table (la cardinalité de l’index rejoint le nombre de lignes de la table).

• INDEX_SUBQUERY

Remplace la sous requête par les lignes correspondantes à l’index pas forcément unique.

• UNIQUE_SUBQUERY

Tout comme INDEX_SUBQUERY, mais plus rapide car l’index est unique.

• INDEX_MERGE

Avant la version 5.0 de MySQL, un seul index ne pouvait être utilisé par requête. Maintenant, il est possible d’en utiliser plusieurs dans certains cas : quand le nombre d’enregistrement n’est pas trop élevé et si la requête s’y prête.

• REF, REF_OR_NULL

Recherche des valeurs indexées (nulles ou pas) pas forcément uniques dans la table.

• EQ_REF

Utilise le résultat d’une partie de la requête, qui sert de base pour compléter la suite de la requête (pour satisfaire un AND par ex).

• CONST

On recherche dans une colonne indexée et unique une constante.

Cette liste n’est pas exhaustive, je vous invite à consulter la doc MySQL pour tous les détails.

La partie rows renvoyée par EXPLAIN nous donne une approximation du nombre de lignes qui devront être lues pour satisfaire la requête. Il est nécessaire, pour que le calcul soit crédible, qu’on ait effectué un ANALYZE TABLE ou un OPTIMIZE TABLE sur les tables concernées par l’opération.

Une autre donnée importante nous est renvoyée par EXPLAIN, la colonne extra, dont voici quelques résultats intéressants :

• using_filesort

Un tri doit être effectué par MySQL dans un deuxième temps, et l’index correspondant au tri n’existe pas.

• using_index

La requête n’a pas besoin de lire dans la table, elle ne lit que les informations de l’index (ex : SELECT id FROM table;).

• using temporary

Souvent couplée avec using_filesort, MySQL crée ici une table temporaire en mémoire pour pouvoir effectuer ses calculs.

• using intersect

Lorsqu’on utilise plusieurs index (index_merge) dont la PRIMARY KEY, et que la table ne contient pas trop de lignes.

• Influencer l’optimiseur :

Bien souvent, l’optimiseur ne débrouille très bien tout seul, mais il peut arriver, pour des requêtes complexes, ou pour une raison x ou y, qu’on ait besoin d’orienter les choix faits par l’optimiseur MySQL pour ses méthodes de sélections.

• USE INDEX

Indique à MySQL de préférer tel index plutôt qu’un autre, quand le choix est possible (... FROM table USE INDEX (nomIndex) ...).

• FORCE INDEX

Comme le USE INDEX, mais indique en plus à MySQL de ne pas choisir l’option de parcourir séquentiellement la table quand l’index demandé est accessible (il existe).

Enfin, pour certaines requêtes, plus complexes à optimiser, MySQL demande plus de calculs pour choisir la meilleure option. Mais dans ce cas, les performances peuvent s’en retrouver amoindries. Deux stratégies sont configurables :

• optimize_prune_level

1 : évite d’étudier certaines stratégies qui provoquent la lecture d’un trop grand nombre d’enregistrements.
0 : Demande à MySQL d’examiner plus de possibilités

• optimizer_search_depth

Un grand nombre permet de passer plus de temps à chercher la meilleure solution (car analyse de plus de tables).
Un petit nombre diminue les chances de trouver la meilleure solution, mais fournit rapidement une stratégie.
Enfin, défini à 0, c’est l’optimiseur qui définit la marche à suivre.