When developing a content system, it is quite common to need to know the total number of results during a search. This makes it possible, for example, to use a pagination system to navigate in the content. This article presents 2 methods to retrieve the number of results during a search. One way is to perform the query and perform a second with COUNT
Concrete case of a pagination system
To better situate ourselves in this article, we will take a concrete example. Imagine a blog that has several hundred articles. Such a blog has a pagination on the home page to navigate through the list of all articles. In our case we will consider that each page has 10 articles.
Method with COUNT
SELECT * FROM `article` ORDER BY id DESC LIMIT 10;
The ordinary way to use a paging system is to use 2 separate queries.
SELECT COUNT(*) AS nombre_article FROM `article`;
The first query retrieves the first 10 articles. This can be done for example with this query:
The second request is to count the maximum number of articles. This allows you to know the number of pags in the pagination system:
SELECT SQL_CALC_FOUND_ROWS * FROM `article` ORDER BY id DESC LIMIT 10;
Method with SQL_CALC_FOUND_ROWS (with MySQL)
SELECT FOUND_ROWS() AS nombre_article;
The SQL_CALC_FOUND_ROWS option is available with MySQL and allows to count the maximum number of results without taking into account the LIMIT. This option is to be used immediately after the SELECT command as follows: Once the query has been executed, you must then use the FOUND_ROWS () function to retrieve the number of results of the query without taking into account the limit.
to know the total number of pages it is possible to calculate using the ceil function (number_article / 10) (where 10 is the number of articles per page).
Potential error with multiple queries using SQL_CALC_FOUND_ROWS
There can easily be errors if there are multiple queries using SQL_CALC_FOUND_ROWS that are used. Let’s imagine that on the same page there is a query retrieving the list of articles and another retrieving the list of users. If the SQL_CALC_FOUND_ROWS is launched on the query which retrieves the articles and that just before retrieving the result with FOUND_ROWS there is the query retrieving the users (with SQL_CALC_FOUND_ROWS) then the result will be that of the number of users and not that of the number of ‘article.
Performance of the 2 methods
It’s quite astonishing but it turns out that the performances are a little more interesting using the method with the COUNT
. However, the indexes must be used correctly on the WHERE and ORDER BY commands.
The difference in performance is even more noticeable when the cache is enabled. In fact, imagine that a user browses several pages of the site to go to pages 2, pages 3… Therefore the requests will be executed each time. However, the query which consists in counting the number of articles with COUNT will always return the same result. Therefore, the server will need less resources to know the number of articles.
The option is available since MySQL version 4.0.0. This option is very practical, especially in the case of a search to find out how many lines have the keyword sought.