MySQL Forums
Forum List  »  Newbie

Re: Query performance on database with joins
Posted by: Rick James
Date: December 24, 2008 10:33PM

ORDER BY ... LIMIT is the obvious way to do pagination. But it has issues.
* As you flip thru the pages, the queries get slower.
* If you have join problems (like you seem to), all the queries are slow.
* If someone inserts/deletes an item while someone else is paging thru them, the pagination is off -- an item will be missing or duplicated.

I recommend finding a unique value that you can use for "left off". Then, instead of
SELECT ... ORDER BY ... LIMIT 100, 20;
you have
SELECT ... WHERE xx > $leftoff ... ORDER BY ... LIMIT 20;

Options: ReplyQuote

Written By
Re: Query performance on database with joins
December 24, 2008 10:33PM

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.