Mar
27
27
Limiting your result set, but getting count of all rows
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)
This is indeed quite handy, but performance can be surprising: See http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
Cheers,
Peter
Comment by Peter Romianowski — March 27, 2008 @ 11:32 am
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.
Comment by mike — March 27, 2008 @ 12:29 pm
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.
Comment by Sheeri — March 28, 2008 @ 6:49 am