Re: Efficient MySQL for pagination of large amounts of data on website

[Barry Galbraith]

Nothing so involved.

mysql> select sql_calc_found_rows entry_time from t1 limit 0,2;
+---------------------+
| entry_time          |
+---------------------+
| 2010-08-14 10:00:00 |
| 2010-08-14 10:00:00 |
+---------------------+
2 rows in set (0.02 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            8 |
+--------------+
1 row in set (0.00 sec)

The first call, with SQL_CALC_FOUND_ROWS, retrieves the rows according to LIMIT.
The second call retrieves the number of rows that would have been returned if not for the LIMIT.

You can retrieve one page of data at a time, and found_rows() will show you how many rows there are in total, so you can calculate how many pages it will take to show all your rows.

Good luck,
Barry.