MySQL Forums
Forum List  »  Newbie

Re: Efficient MySQL for pagination of large amounts of data on website
Posted by: Miloš Rašić
Date: October 08, 2010 07:55AM

Remembering the last id of the previous page is a great idea for cases when you have only a simple listing with previous and next links. How would you solve the problem when the user is able to jump to any page at any time?

I had an idea to run through the results of the non-LIMITed query once and form a table of first item for every page, then store it in a cookie with a unique hash for the search, but the problem with this is that cookie would be rather large for large table. Also, the table stored in the cookie would no longer be valid in case the database was changed by another user.

Another idea I had is to just slightly optimize the whole process by remembering the last id (or whatever the result is sorted by) of the current page, so that the query can be optimized in case the user jumps to a page with higher number. It would still use OFFSET but with addition of WHERE sort_field > last_sort_field_value which could significantly reduce the size of the result and thus the time needed for MySQL to reach the offset row. The problem with this approach is that I think it would actually slow down the pagination with small tables, and I want my solution to be fully scalable.

Any ideas?

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.