Re: Optimal mySQL query for next prev page system

Adam i Agnieszka Gasiorowski FNORD wrote:

Kim Steinhaug wrote:

Then I query the mySQL database again to get the final result set, which I return together with the buildt prev/next htmlkode

Instead of querying twice, use the SQL_CALC_FOUND_ROWS parameter of SELECT statement and then do a quick SELECT HIGH_PRIORITY FOUND_ROWS(); , to get the number of found rows (full number, ignoring LIMIT clause).

You could also just do a SELECT COUNT(id) ... as your first query to just get the number of rows. Then do your query with limit. This is going to be more effecient than what you're currently doing. It'll also be more portable than the CALC_FOUND_ROWS method (although that's a perfectly valid method if you're sticking with MySQL). I'd have to run some benchmarks to see which is faster... -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ php|architect: The Magazine for PHP Professionals - www.phparch.com