MySQL Limit Pagination is Slow on Large Offset Quantities - How do I resolve?

With MySQL field indexing on fields you regularly use in WHERE clauses, you can speed up your performance.

You can also use LIMIT to either limit your results, or to get paginated results. If you show 20 results per page and you want page 7's results, you would select with...

MyDB@sys mysql> SELECT * FROM ... LIMIT 7, 20;
...
20 rows in set (0.01 sec)

But this query becomes intensely slow with higher offsets, such as this query...

MyDB@sys mysql> SELECT * FROM ... LIMIT 50000, 20;
...
20 rows in set (0.35 sec)

The same exact query, using different offset limits, has very different performance!

But it is very easy to get around! Just use a subquery to select the id's first, which has your LIMIT.

MyDB@sys mysql> SELECT * FROM ... WHERE id IN(SELECT id FROM ... LIMIT 50000, 20);

Bypassing this problem shouldn't really work, and yet it does! That is because this has been a bug with MySQL for about the past decade: https://bugs.mysql.com/bug.php?id=41871