MySQL Forums
Forum List  »  Newbie

Re: Efficient MySQL for pagination of large amounts of data on website
Posted by: Rick James
Date: August 17, 2010 07:28AM

Yes, it can be done in MySQL:,246731 (Alternative to FOUND_ROWS()),243777 (Query performance question (using index slows down?)),239876 (Query performance on database with joins),360578 (join in union),355941 (Query that returns indexed fields involves FILESORT, why?),291646 (Very Slow Simple Query),292296 (break mysql query? Is this possible in my sql),240838 (Slow search query),234339 (MySQL Performance Tuning on my server)

The idea is to remember where you "left off", then do
WHERE id > @left_off LIMIT 10

Whether to do > or >= ? That gets into the details. Also I like to do LIMIT 11 for 10 items, plus discovering whether there is a "Next" page.

This technique also avoids the pseudo-bug that happens when an item is DELETEd or INSERTed while the user is reaching for the Next button. With LIMIT N,M an item could be skipped or duplicated.

A temporary table is unscalable in multiple ways:
* What if there are a million items? It will take an awful amount of time to build.
* Where will you put the may table for your many lists?
* When do you drop the table?

Related topics:
* Displaying "Items 11-20 of 8765" -- This could use FOUND_ROWS(), but leads to scaling problems (as alluded to in some of those links)
* Next and Prev are easy. First and Last take some extra code, but are also easy. Jumping directly to Page 123 should not be allowed in your UI. OTOH, jumping to any of the Next/Prev 5 pages is not costly, but takes some more design work.
* If the items are ordered by a single column, all is nice. If there are multiple columns or there is filtering (eg, WHERE hidden=0 AND published=1), the performance may suffer.
* If you have to JOIN two tables to do the filtering -- Well, this may totally destroys any hope of performance.

Show me your specifics (SHOW CREATE TABLE, SELECT, etc) and I may have more pointers.

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.