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
How is this really different from count(*) without the limit clause?
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...
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.
Read the docs - I see now. I don't believe there is an equivalent in sql server. That is a nice feature though!
That would be sweeeeeet in SQL Server. I think it would change the way I did pagination.
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.
We use SQL_CALC_FOUND_ROWS, LIMIT, and OFFSET for pagination. Pretty slick.
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.
@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!
I know, great! I filed the "bug/feature" back in January.
http://bugs.mysql.com/bug.p...
But thanks for the notice ;)