You are here: Home / Technologies / Databases / Limiting your result set, but getting count of all rows

Limiting your result set, but getting count of all rows

by

In reviewing some of my notes I thought it was relevant to ensure this small MySQL gem is documented for my history purposes.

Historically I’ve seen people do a complex SELECT statement twice, first to get a COUNT(*) and then to retrieve the data, perhaps in a paginated format. In MySQL you can combined both SELECT statements into 1 SQL. You still need a second SQL statement, but it is less impactful to your system.

Here is how?

mysql> select sql_calc_found_rows * from mysql.user limit 2;
...
2 rows in set (0.01 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            5 |
+--------------+
1 row in set (0.00 sec)

Comments

  2. says

    I wrote a wrapper that does this for pagination purposes. Been using it since 2002 or so…

    However the link below has some benchmarks showing that it can be faster to just do two full separate queries, which made me sad since I had thought my little wrapper was so ingenius :)

    http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

    I do still use it though, since it’s easy and I haven’t needed to hit that level of performance optimization yet.

  3. says

    actually I was going to post a trick I saw a developer do — he just did a SELECT with LIMIT 0,page+1 where page was the number per page.

    So let’s say a page paginated 20 “entries” — use LIMIT 0,21 and then use your application to count how many results you got back. If you got more than 20, then display the “next page” link. This could be combined with a count(*) later in the page, after the entries are already displayed, to do the processing without interrupting the user’s experience.