Getting the total number of matching rows from MySQL when using LIMIT

It’s an extremely common requirement, when designing an API or GUI that includes pagination, to provide 10 rows at a time, but also include a figure of the total number of matching rows. Traditionally people do this by running two requests: first the query, then the same conditions with a COUNT(*) instead. This however is two potentially heavy DB hits. There is a quicker and easier way, and it’s laughably simple.

First, in your SQL, add the SQL_CALC_FOUND_ROWS modifier in front of your SELECT:

SELECT SQL_CALC_FOUND_ROWS name, price FROM fruits LIMIT 10;

Then, after running your query, immediately run the FOUND_ROWS() query:

SELECT FOUND_ROWS();

Done! The second query is returned instantly using the metadata returned from the first, with no additional heavy lifting on the DB’s part. Brilliant!

FOUND_ROWS() works no matter how complicated your SELECT statement. Note that with a GROUP_BY it will return the number of rows in the result, not the number of rows matched before they were grouped. This behaviour is usually going to be more useful to you anyway.

Thanks to ArrayStudio for the well-written tip.

Python errors running s3cmd on Amazon AWS

Whenever I ran s3cmd on my AWS instance, I got the following errors:


/usr/lib/python2.6/dist-packages/S3/S3.py:9: DeprecationWarning: the md5 module is deprecated; use hashlib instead
/usr/lib/python2.6/dist-packages/S3/S3.py:10: DeprecationWarning: the sha module is deprecated; use the hashlib module instead


The problem is that this is an old, crappy version of s3cmd, 0.9.8.4. You can get version 0.9.9 using the instructions in the linked blog post. It’s just a matter of adding a new source to your apt-get sources list and then running apt-get update.