Correction des requêtes lentes

Dans notre dernier article nous avons identifié nos requêtes lentes et nous avons compris pourquoi elles l’étaient grâce à EXPLAIN. Maintenant il est temps de comprendre comment on peut corriger ces problèmes.

Option 1. Modifier la requête.

Sur le site CSS-Tricks nous avons une requête qui exécute très lentement. Cette requête est une partie des métas box de Custom Fields. Voici la requête SQL:

01 SELECT meta_key
02 FROM wp_postmeta

 

03 GROUP BY meta_key
04 HAVING meta_key NOT LIKE ‘\\_%’

 

05 ORDER BY meta_key
06 LIMIT 100

Ce fragment de code SQL, permet de recevoir une liste de meta_keys de la table wp_postmeta. Les meta_keys qui nous intéressent ne doivent pas commencer par un underscore «_». GROUP BY signifie que chaque résultat est unique.

En exécutant cette requête 5 fois cela nous prend :

1.7146 sec
1.7912 sec
1.8077 sec
1.7708 sec
1.8456 sec

Pourrait-on écrire une autre requête qui permettrait d’obtenir le même résultat ? Nous devons sélectionner des meta_keys uniques. Le mot unique a pour synonyme distinct qui fait partie des opérateurs SQL.

En utilisant l’opérateur DISTINCT nous pouvons faire ce qui suit :

01 SELECT DISTINCT meta_key
02 FROM wp_postmeta

 

03 WHERE meta_key NOT LIKE ‘\\_%’
04 ORDER BY meta_key

En exécutant notre requête modifiée quelquefois on obtient le résultat suivant :

0.3764 sec
0.2607 sec
0.2661 sec
0.2751 sec
0.2986 sec

Cette comparaison montre une amélioration importante!

Option 2. Ajout d’un index.

Quand vous executez une requête SQL vers une table MySQL standard, MySQL scanne toute la table pour définir quelles lignes correspondent à cette requête. Si votre table est très grande, ce scan prendra beaucoup de temps.

Dans ces cas-là les index MySQL aident beaucoup. Les index prennent les données des tables et les organisent de manière à faciliter la recherche.  En organisant les données de cette façon les index aident à réduire la durée d’un scan MySQL pour chaque requête.

Les index peuvent être ajoutés à une ou plusieurs colonnes. La syntaxe est la suivante :

01 CREATE INDEX wp_postmeta_csstricks ON wp_postmeta (meta_key)

Avec un index pour meta_key notre requête SQL originale s’exécutera en :

0.0042 sec
0.0024 sec
0.0031 sec
0.0026 sec
0.0020 sec

La difference est evidente!

Soyez prudent en utilisant les index. Chaque fois que nous créons une ligne grâce à INSERT ou que nous utilisons UPDATE sur une table indexée, l’index est calculé de nouveau. Cette opération prend du temps. Les index permettent une lecture des tables plus rapide mais rendent l’écriture plus lente. Les index placés au bon endroit peuvent accélérer considérablement vos requêtes mais il vaut mieux étudier leur influence globale sur votre base des données avant de les utiliser.

Option 3. Mettre les résultats des requêtes en cache

Nous savons que nous avons des requêtes lentes. Au lieu de les modifier nous pouvons sauvegarder les résultats de ces requêtes. Ansi nous limiterons l’execution des requêtes et nous obtiendrons souvent une réponse rapide.

Pour mettre une requête en cache nous pouvons nous servir de WordPress Transients API. Transients est utilisé pour sauvegarder les résultats d’opérations compliquées telles que :

  • Les requêtes vers les sites externes (sélection des posts Facebook récents par exemple)
  • Blocs de traitement lents (la recherche de longues lignes à l’aide d’expressions régulières par exemple)
  • Requêtes lentes vers la base de données

La sauvegarde des résultats des requêtes grâce à transients ressemble à :

01 if ( false === ( $results = get_transient( ‘transient_key_name’ ) ) ) {
02   $results = …; // Do the slow query to get the results here

 

03   // 60 * 60 is the expiration in seconds – in this case, 3600 seconds (1 hour)
04   set_transient( ‘transient_key_name’, $results, 60 * 60 );

 

05 }

Sauvegarder les résultats d’une requête signifie qu’elle sera exécutée une fois par heure. Cela nous conduit à un avertissement très important sur l’usage de transient: Soyez prudent en utilisant transient avec les données qui changent souvent.

Si vous avez des requêtes dont les resultats ne changent pas trop souvent, utilisez transient est une méthode excellente pour éviter d’accéder fréquemment à la base de données.

Choix de l’approche

Nous venons d’étudier trois options de correction des requêtes lentes. Il y en a encore probablement 17 autres pour résoudre le problème des requêtes lentes. Mais laquelle choisir ?

La première option donne des résultats excellents mais que faire si la requête ne donne pas toujours les mêmes résultats ? Nous pouvons involontairement modifier notre code avec ces requêtes.

La seconde option n’est pas toujours possible en fonction de la table et des colonnes utilisées par la requête (requêtes INSERT et UPDATE).

La troisième option (transients) a une influence minimale. Nous n’avons pas à modifier le code ou la structure de la base des données, mais les données ne doivent pas changé trop souvent.

En bref, il n’y a pas de solution adaptée à tous les cas. Le choix de l’option dépend beaucoup du problème SQL à résoudre.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *