MySQL Forums

when pulling a large amount of data and paging it, what is the most efficient way to set up the queries. i need to get the total number of records and the data for the rows that will display on the current page.


i usually use 2 queries, 1 to count the number of records in the entire set and 1 to return the results to display on the current page

something like to get the total
"select count(*) as total_contacts from contacts;"

and then something like this to get the results
$per_page=20;
$page=2;
$first_result=($page-1)*$per_page;
"select * from contacts limit $first_result, $per_page order by last_name;";


the other option would be to simply get all results
"select * from contacts order by last_name;"
then count the total records that are returned and only show the results in the current page


the first example is wasteful because it has to go through the data twice

the second is wasteful because it could potentially pull a very large amount of data that is never used. every single record has to be retrieved, but only 20 will be displayed.


is there a better way to do this?