I was curious, which is the faster and best method when paginating data and why, using the SQL_CALC_FOUND_ROWS in your select statement then later calling the SELECT FOUND_ROWS() verses using a COUNT() to get total records.

Thank You
mike

__________________
Peace
Mike
http://www.eclecticpixel.com

harpua,
I am not sure about the functions you are asking.
My suggestion is to try doing it by time trial,
Let say, have a Time difference query or function to
know in actual datas. Like an onclick button in PHP
will show you the time difference from one to the other.
An example is this with series of sqls to execute.
GROUP 1... COUNT() ----> GET RESULT
1) FUNCTION BY COUNT:
SELECT COUNT(*), NOW() AS TimeBefCount FROM tablename;
exec....
assign to a variable cn1 = countresult
                     tm1 = timebefcount

2) Add another sql INSERT
exc...

3) REDO NO. 1

4) CALCULATE THE DIFF BETWEEN RESULT OF 3 AND 1.

GROUP 2... SQL_CALC_FOUND_ROWS ----> get result
ETC...

hope this helps.
john

When all columns in the WHERE clause are fully indexed then the fastest method is to run 2 selects:

SELECT * FROM table WHERE col=x LIMIT 20
SELECT COUNT(*) FROM table WHERE col=x

This allows the Count to be found from just the index

SQL_CALC_FOUND_ROWS on the other hand uses filesort/temporary tables and not the index and consequently is slower

This is a known bug/feature request http://bugs.mysql.com/bug.php?id=18454

On the other hand, experience/benchmarking has shown me that the situation can be reversed when columns in the WHERE clause are not fully indexed and COUNT(*) is forced into a table scan.

By the way, to benchmark your queries you use EXPLAIN
http://dev.mysql.com/doc/refman/5.0/en/explain.html