J’ai récemment été confronté à un problème, la présence d’un ORDER BY multipliait par 12 le temps d’exécution d’une requête, la rendant pratiquement inutilisable.
Nous allons voir quels sont les problèmes de performance et comment optimiser notre requête.

Explications sur le problème

En fait, le problème à l’origine n’était pas la clause ORDER BY mais une sous-requête (subquery) qui était exécutée dans la clause WHERE.
La requête disposant d’une clause LIMIT assez basse, MySQL est optimisé pour n’exécuter cette clause WHERE que s’il n’a pas assez de résultat pour remplir sa clause LIMIT. Avec la clause ORDER BY, on perd cette optimisation, il va exécuter cette sous-requêtes sur toutes les entrées de votre table, même si elle en a 100 000, puis après faire le ORDER BY puis enfin le LIMIT. Cela nuit donc très fortement aux performances.

Cette requête a un temps d’exécution moyen de 72 secondes dans mon cas.

La solution

Les solutions, j’en ai pas trouvé 10 000, la première résout partiellement le problème, il suffit d’ajouter dans la clause WHERE une condition sur le même champs que la clause ORDER BY, ça limitera les dégâts sur les performances.

La seconde est réellement efficace, elle consiste à exécuter la condition de la WHERE clause contenant la sous-requête après TOUT le reste.
Pour faire cela, on est obligé d’utiliser une nested query, c’est à dire de faire sa requête dans le FROM d’une autre requête (ce qui est une sous-requête mais dans la clause FROM). On a donc une requête optimisée, ayant des temps d’exécution beaucoup plus faibles.

Cette requête a un temps d’exécution moyen de 0.4 secondes dans mon cas.

Attention à bien laisser la clause ORDER BY dans la requête imbriquée, sinon votre requête ne sera pas entièrement optimisée.