[MySQL] Using SQL_CALC_FOUND_ROWS and FOUND_ROWS with MySQL

Hello,
for this second blog article I've decided to explain this neat little feature of MySQL: SQL_CALC_FOUND_ROWS and FOUND_ROWS().
This article is about MySQL only, it is likely that these keywords/functions exist in other SQL-based languages but I've only ever used them with MySQL.

What's the point, you ask?
When working with (my)SQL databases, you often find yourself using the LIMIT keyword, in order for example to limit the results of a large search. Let's study a particular case.
- You're working on the "employees" table, which contains, say 1000 data rows.
- You wish to display the list of employees in a particular department, 10 by 10.
- You wish to display the amount of employees in this department.

What would you normally do?
Here's a rough example:
$result = mysql_query("SELECT * FROM employees WHERE department='sales' LIMIT 0,10 ");
$total = mysql_num_rows($result);
-> This is incorrect because $total will never exceed 10 due to the LIMIT clause.

You could also do this:
$result = mysql_query("SELECT COUNT(*) cnt FROM employees WHERE department='sales'");
$count = mysql_result($result, 0, "cnt");
$result = mysql_query("SELECT * FROM employees WHERE department='sales' LIMIT 0,10 ");
-> But this means you have to process two SQL queries.


So how do you fix this?
Using the SQL_CALC_FOUND_ROWS keyword in your SQL query will allow you to fetch the total amount of results without being limited by the LIMIT clause.
Here's how you use it:

$data_result = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM employees WHERE department='sales' LIMIT 0,10");
$count_result = mysql_query("SELECT FOUND_ROWS() cnt");
$employees_count = mysql_result($count_result, 0, "cnt");
echo "$employees_count employees found in the sales dept. Showing first 10: ";
...

As you can see, using the SQL_CALC_FOUND_ROWS keyword allows you to save the amount of results before applying the LIMIT clause; and this amount can be retrieved with a simple "SELECT FOUND_ROWS()" after the query is executed.

I've found this to be pretty useful when designing search pages, directories, and so on...
If you want more information about the FOUND_ROWS() function, and other useful functions aswell, check this page on the official MySQL documentation.

Okay, that's all folks!

Comments

Anonymous said…
Great explanation !

After hours of searching for information how to use this FOUND_ROWS() thing, I finally found your page.
And with this clear and complete example I finally got it working in no time.

Thanks a lot !
BobM said…
A somewhat old post, but mat be worth a read: http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
Unknown said…
Great post. i like it. feeling great when reading your post . juegos.com | juegos de matar | jogos de friv
unblocked games said…
Guidelines or that, I would try play unblocked games very nice , free unblocked games online to play , descargar whatsapp gratis chat limit , unblocked games , unblocked games , descargar whatsapp gratis
story with the young man who receives Geneva that he and you love each other
descargar whatsapp fast , use app baixar whatsapp very comfortable , play mobogenie games hot descargar whatsapp gratis , baixar whatsapp gratis , descargar mobogenie gratis

Popular posts from this blog

Affiliate module for Interspire Shopping Cart

How to fix: Outlook on iOS/iPhone: "No Internet Connection"

Dealing with Nginx 400 Bad Request HTTP errors