Chez Octopuce, en tant qu’hébergeur à forte valeur ajouté, nous aidons souvent nos client à optimiser leur MySQL, leur Apache, leur PHP, ou d’autres services du même genre.
Nous tombons parfois, pour nos clients les plus gros, sur des problèmes qui peuvent aller jusqu’à mettre à mal leur performance, et nos compétences purement système ne suffisent plus.
Aujourd’hui, je vous propose un exemple simple : comment optimiser une requête MySQL complexe et gagner énormément de temps à la génération d’une page web.
Pour cela, on utilise classiquement la directive EXPLAIN de MySQL, qui permet de demander à MySQL ce qu’il fera quand il exécutera une telle requête. On l’utilise en ajoutant le mot clé EXPLAIN avant un SELECT.
Prenons un exemple réel (légèrement modifié pour anonymiser le client) :
SELECT DISTINCT(F.ref),F.date_creation, CONCAT(P.nom,' ',P.prenom) FROM client P, formulaire F, suivi S WHERE P.ref = F.ref AND F.ref = S.ref AND S.etat = 1 AND NOW()>=DATE_ADD(F.date_creation, INTERVAL 5 MINUTE) AND NOW()<=DATE_ADD(F.date_creation,INTERVAL 1 DAY);
On remarque que cette requête effectue une jointure sur 3 tables, avec un filtrage (WHERE) complexe et un DISTINCT() pour couronner le tout…
Savoir si cette requête utilisera bien les index présents dans la table est complexe. Pour rappel, on met les index d’une table dès qu’on a l’intention de faire une requête utilisant ce champ dans la clause WHERE.
En ajoutant EXPLAIN, on obtient :
EXPLAIN SELECT DISTINCT(F.ref),F.date_creation, CONCAT(P.nom,' ',P.prenom) FROM client P, formulaire F, suivi S WHERE P.ref = F.ref AND F.ref = S.ref AND S.etat = 1 AND NOW()>=DATE_ADD(F.date_creation, INTERVAL 5 MINUTE) AND NOW()<=DATE_ADD(F.date_creation,INTERVAL 1 DAY);
MySQL nous dit ainsi, pour chaque élément du WHERE (ou le distinct ici) quel index il pourrait utiliser (possible_keys) et quels index il utilise effectivement (key). Ce faisant, il nous déduit (dans rows) que le nombre de lignes qu’il devra scanner pour cette requête est de 500 000 ! Le temps d’une telle requête est, sur un serveur optimisé, de 2.3 secondes…
On voit donc une chose intéressante : dans cette partie du WHERE :
AND NOW()>=DATE_ADD(F.date_creation, INTERVAL 5 MINUTE) AND NOW()<=DATE_ADD(F.date_creation,INTERVAL 1 DAY);
Nous utilisons le champ F.date_creation. Or ce champs a bien un index comme nous le montre
SHOW CREATE TABLE formulaire; ... PRIMARY KEY (`ref`), KEY `date_creation` (`date_creation`), ...
Alors pourquoi MySQL n’utilise pas cet index ? Tout simplement parce que dans la requête, on passe ce champ à travers une fonction (ici DATE_ADD), l’index ne peut donc pas être utilisé, puisqu’on ne cherche pas une valeur ou un plage de valeur précise de ce champ !
On peut donc essayer de permettre à MySQL d’utiliser cet index en inversant la condition :
AND NOW()>=DATE_ADD(F.date_creation, INTERVAL 5 MINUTE) AND NOW()<=DATE_ADD(F.date_creation,INTERVAL 1 DAY);
devient alors
AND DATE_SUB(NOW(), INTERVAL 5 MINUTE)>=F.date_creation AND DATE_SUB(NOW(),INTERVAL 1 DAY)<=F.date_creation;
et EXPLAIN de notre SELECT nous retourne désormais :
Et la durée de la requête tombe à 0.02 secondes !
La règle à suivre est donc :
Dans la mesure du possible, toujours éviter les appels de fonction portant sur un champ, si on peut mettre l’appel de fonction de l’autre côté du test.
Par ailleurs, il est parfois utile d’inverser l’appel de fonction même si on a un champ des deux côtés :
si on a un
DATE_SUB(t1.date_creation, INTERVAL 7 DAY) <= t2.date_creation
on peut essayer de le remplacer par
t1.date_creation <= DATE_ADD(t2.date_creation, INTERVAL 7 DAY)
Cela peut s’avérer utile si la création d’un index sur t1.date_creation est trop coûteuse (grosse table, ou table souvent modifiée) ou si un index existe déjà pour t2.date_creation, ou encore si la cardinalité de t1 est plus forte que celle de t2 !