Pagination

LIMIT/count query or not?

[Warboss Alex]

I've got a 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!

Cheers,
Alex ...

[mcibor]

I think it depends on how big your database will be and on how many records will you be downloading in one query.

Database is optimised to handle big amounts of data, whether PHP is not. So if you are about to download millions of records and then paginating them I would do it with database and LIMIT.

If there are about 100 answers, you may choose to paginate on PHP. BTW databases usually do only one query at the time, whether PHP is controlling more users.

In my application (5000 records, in one question usually 300 records) I use LIMIT.

Have fun!