MySQL Tip - Finding total rows for a query that uses Limit

So I'm constantly surprised by MySQL. I ran across a stored procedure today that had this in it:

SELECT SQL_CALC_FOUND_ROWS t.themeID....

A quick google search turned up this doc that describes this feature. Basically, you can use this to tell MySQL to figure out how many rows would have been returned in a query if you had left off the limit operator. It obviously slows down the query, but is not as slow as rerunning the query without the limit. (And here is a scary question - have you ever seen code that did total counts like that?) To use this feature you have to follow up with an immediate select found_rows(). Check the documentation for more information.

Anyone know if SQL Server has something like this?

Archived Comments

Comment 1 by Joel Cox posted on 1/24/2007 at 10:26 PM

How is this really different from count(*) without the limit clause?

Comment 2 by todd sharp posted on 1/24/2007 at 10:45 PM

I'm not a MySQL user - is limit like top?

Anyways, in SQL server you can follow up a query with select @@rowcount to get the rows affected by the last query (including inserts, updates, deletes). PS - I'm sure you know this, but CF now returns a recordcount for ins, upds, dels...

Comment 3 by BL posted on 1/24/2007 at 10:59 PM

I have always been looking for a better implementation of this concept and I did not know about MySql's SQL_CALC_FOUND_ROWS feature. I wonder if it's only on MySql version 5.x. The way I've been doing this concept is to first run a select count(*) with the same from and where clauses and no limit (because count(*) executes much faster), then do the regular select with the limit.

It works pretty fast, but I've wondered myself if this is the best implementation of the concept.

Comment 4 by todd sharp posted on 1/24/2007 at 11:06 PM

Read the docs - I see now. I don't believe there is an equivalent in sql server. That is a nice feature though!

Comment 5 by Ben Nadel posted on 1/25/2007 at 1:38 AM

That would be sweeeeeet in SQL Server. I think it would change the way I did pagination.

Comment 6 by Per K. posted on 1/25/2007 at 4:23 AM

When you do [select * from...] in CF/MySQL, CF executes [show full columns from...] behind the scenes. That screws up this function on MySQL 5.x (on 4.x it works fine)

Try this on MySQL 5.x:

select SQL_CALC_FOUND_ROWS * from entries limit 2;
show full columns from entries;
select found_rows();

found_rows(); actually returns the result from the previous query, that would be the number of fields in the table in question.

Bummer...

I've tried different versions of Connector/J - no luck.

Comment 7 by Brad Roberts posted on 1/26/2007 at 6:43 AM

We use SQL_CALC_FOUND_ROWS, LIMIT, and OFFSET for pagination. Pretty slick.

Comment 8 by Robin posted on 2/20/2007 at 8:48 PM

I can tell you from experience that select count(0) is three times faster than SQL_CALC_FOUND_ROWS on larger tables.

count(0) is faster than count(*) by the way.

Comment 9 by Jack Welde posted on 7/22/2007 at 9:12 AM

@Per K -- this problem has been solved with the latest version of MySQL Connector/J (v.5.07 as of today). With that JDBC connector, you can select found_rows() and it will work properly.

Note that I had to add "useDynamicCharsetInfo=false" in the Connection String of the datasource (I'm using BlueDragon, but I'm sure that ColdFusion administrator has the equivalent).

Hope that helps!

Comment 10 by Per K posted on 7/22/2007 at 8:31 PM

I know, great! I filed the "bug/feature" back in January.

http://bugs.mysql.com/bug.p...

But thanks for the notice ;)