Find the Total number or rows with a Limit

By Hawkee on Apr 15, 2012

This is a very handy command to avoid running the same query twice. It will tell you how many total rows there are when you only need a handful. You need to run the second query immediately following the first. If you leave out SQL_CALC_FOUND_ROWS from the first query FOUND_ROWS() will simply return the number or results you got, 10 in this case.

select SQL_CALC_FOUND_ROWS field1, field2 from table where field1 = '1' limit 0,10;

select FOUND_ROWS();

Comments

Sign in to comment.
Hawkee   -  May 04, 2012

@sean Yes, or even my.cnf optimizations.

 Respond  
sean   -  May 04, 2012

@Hawkee yup, very handy.

After deliberation, it's rather difficult to post generalized MySQL snippets since most queries are quite specific. I guess this section would be more for tutorials/examples/tips?

 Respond  
Hawkee   -  May 02, 2012

Yes, I just wish I knew about it sooner. This was added to mySQL in 2004 and by that time I had just become accustomed to running two queries. I learned the technique a couple years ago using Sphinx and thought mySQL should have a command like this.

 Respond  
sean   -  May 02, 2012

An excellent blog post about this on this very subject :P http://bit.ly/KQYtX1

 Respond  
Are you sure you want to unfollow this person?
Are you sure you want to delete this?
Click "Unsubscribe" to stop receiving notices pertaining to this post.
Click "Subscribe" to resume notices pertaining to this post.