I've got a PHP class for paginating results of a database query, with page numbers and all the usual trimmings, but there's only one query involved - getting the number of pages for the pagination links, and then navigating through the query to get the results in the area I want.
This involves fetching ALL the records in the table into memory though, and I'm concerned about the overhead this incurs. It's not the scripting, it's more the system architecture I guess .. would having all the records in memory perform better than doing a count(*) query on the data, and then using LIMIT (a second query) to only actually pull the required data into memory.
I'm sure there's arguments for and against, especially when the database gets big. But is there a winner, between the two? I've always tended towards the one-query method because you need the record count anyway, and even in a LIMIT query, MySQL has to traverse all the rows anyway?
Can anyone give me some advice? thanks in advance!