Re: Query performance on database with joins

[Rick James]

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;