I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec:
SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5;
But querying the exact same rows in the reverse takes 2.50sec:
SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id DESC LIMIT 499975,5;
Furthermore, if I don't force index for the second query, MySQL will choose not to use it.
I note that there is a plan to introduce sort order for index, but that does not explain why MySQL cannot use the same index effectively when LIMIT offset is large since it knows the total row count.