MySQL - caching results from query and using again

How to get the total nuber of rows but using LIMIT?

[surfgatinho]

Hi
What I'm currently doing seems really ineffecient. I'm running a query just to get the total number of rows and then running the same query but with a LIMIT to put out the results 15 at a time.
There must be a better way to do this. I thought running a second query on the original might be the way forward but I don't really know where to start.

Thanks in advance
Chris

[ZibingsPrez]

One possible solution would be to run a 'COUNT(*)' query instead of a full 'SELECT' query to get the number of rows in the database:

[pre]
$query_string = "SELECT COUNT(*) FROM `table` WHERE 1";
$query = @mysql_query($query_string);
if (!$query )
{
 //do error handling here
}
else
{
 $count = @mysql_result($query, 0, COUNT(*));
 //do everything you need down here
}
[/pre]

That might help you a LOT, especially if you're looking for efficiency. Hope it helps!

[coopster]

As of version 4.0.0 MySQL has an information function called FOUND_ROWS() [dev.mysql.com] that does exactly what you are describing.

[mgm_03]

i would definitely try out that nifty function ...didn't even know it existed.

otherwise, everywhere I have seen code for paging results involves 2 queries...it may appear inefficient but it is necessary.

[Warboss Alex]

Not really inefficient. You'd need to search ALL the records in the table to get the total number anyway, regardless of how many you fetch into memory.