Nombre de résultats avec/sans SQL_CALC_FOUND_ROWS

Lors du développement d’un système de contenu, il est assez courant de devoir connaître le nombre total de résultats lors d’une recherche. Cela permet par exemple d’avoir recours à un système de pagination pour naviguer dans le contenu. Cet article présente 2 méthodes pour récupérer le nombre de résultats lors d’une recherche. L’une des méthodes consiste à effectuer la requête et à en effectuer une seconde avec COUNT(*) tandis que l’autre méthode utilise une fonctionnalité très pratique de MySQL, l’option SQL_CALC_FOUND_ROWS. Cet article présente les avantages et inconvénients des 2 méthodes.

Logo MySQL

Cas concret d’un système de pagination

Pour mieux se situer dans cet article, nous allons prendre un exemple concret. Imaginons un blog qui possède plusieurs centaines d’articles. Un tel blog possède une pagination sur la page d’accueil pour naviguer à travers la liste de tous les articles. Dans notre cas nous considérerons que chaque page présente 10 articles.

Méthode avec COUNT(*)

La méthode ordinaire pour utiliser un système de pagination consiste à utiliser 2 requêtes distinctes.

La première requête permet de récupérer les 10 premiers articles. Cela peut être réalisé par exemple avec cette requête:

SELECT *
FROM `article`
ORDER BY id DESC
LIMIT 10;

La deuxième requête consiste à compter le nombre maximum d’articles. Cela permet de savoir le nombre de pags dans le système de pagination:

SELECT COUNT(*) AS nombre_article
FROM `article`;

Méthode avec SQL_CALC_FOUND_ROWS (avec MySQL)

L’option SQL_CALC_FOUND_ROWS est disponible avec MySQL et permet de compter le nombre maximum de résultats sans prendre en compte le LIMIT. Cette option est à utiliser juste après la commande SELECT de la manière suivante:

SELECT SQL_CALC_FOUND_ROWS *
FROM `article`
ORDER BY id DESC
LIMIT 10;

Une fois la requête exécutée, il faut ensuite utiliser la fonction FOUND_ROWS() pour récupérer le nombre de résultats de la requête sans prendre en compte la limite.

SELECT FOUND_ROWS() AS nombre_article;

Astuce : pour connaître le nombre total de page il est possible de calculer en utilisant la fonction ceil( nombre_article / 10 ) (où 10 est le nombre d’articles par page).

Erreur potentielle avec plusieurs requêtes utilisant SQL_CALC_FOUND_ROWS

Il peut facilement y avoir des erreurs s’il y a plusieurs requêtes utilisant SQL_CALC_FOUND_ROWS qui sont utilisées. Imaginons que sur une même page il y a une requête récupérant la liste des articles et une autre récupérant la liste des utilisateurs. Si le SQL_CALC_FOUND_ROWS est lancé sur la requête qui récupère les articles et que juste avant de récupérer le résultat avec FOUND_ROWS il y a la requête récupérant les utilisateurs (avec SQL_CALC_FOUND_ROWS) alors le résultat sera celui du nombre d’utilisateurs et non celui du nombre d’article.

Performance des 2 méthodes

C’est assez étonnant mais il s’avère que les performances sont un peu plus intéressantes en utilisant la méthode avec le COUNT(*). Il faut toutefois que les index soient bien utilisés sur les commandes WHERE et ORDER BY.

La différence en terme de performance est encore plus notable lorsque le cache est activé. En effet, imaginons qu’un utilisateur navigue sur plusieurs pages du site pour aller aux pages 2, pages 3 …  Dès lors les requêtes vont s’exécuter à chaque fois. Or, la requête qui consiste à compter le nombre d’articles avec COUNT(*) retournera toujours le même résultat. Dès lors, le serveur aura besoin de moins de ressources pour connaître le nombre d’articles.

A propos de SQL_CALC_FOUND_ROWS

L’option est disponible depuis la version 4.0.0 de MySQL. Cette option est très pratique, notamment dans le cas d’une recherche pour savoir combien de lignes possèdent le mot-clé recherché.