Raymond Camden's Blog Rss

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

10

Posted in Development | Posted on 01-24-2007 | 6,406 views

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

view plain print about
1SELECT 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?

Comments

[Add Comment] [Subscribe to Comments]

Joel Cox said on 01-24-2007 at 11:26 AM #
How is this really different from count(*) without the limit clause?
todd sharp said on 01-24-2007 at 11:45 AM #
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...
BL said on 01-24-2007 at 11:59 AM #
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.
todd sharp said on 01-24-2007 at 12: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!
Ben Nadel said on 01-24-2007 at 2:38 PM #
That would be sweeeeeet in SQL Server. I think it would change the way I did pagination.
Per K. said on 01-24-2007 at 5:23 PM #
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.
Brad Roberts said on 01-25-2007 at 7:43 PM #
We use SQL_CALC_FOUND_ROWS, LIMIT, and OFFSET for pagination. Pretty slick.
Robin said on 02-20-2007 at 9:48 AM #
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.
Jack Welde said on 07-22-2007 at 12: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!
Per K said on 07-22-2007 at 11:31 AM #
I know, great! I filed the "bug/feature" back in January.

http://bugs.mysql.com/bug.php?id=25978

But thanks for the notice ;)

[Add Comment] [Subscribe to Comments]