Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Because it doesn't know the total row count? –  ypercube Feb 21 at 13:21
    
@ypercube, MyISAM stores the exact row count. So it should have an idea on the total row count. –  Question Overflow Feb 21 at 13:30
    
I thought the question was not specific about MyISAM. –  ypercube Feb 21 at 13:47
    
@ypercube, I have already given it a MyISAM tag. I didn't make it more explicit because I wasn't 100% sure that it need to know the row count or that if this is also applicable to InnoDB etc. –  Question Overflow Feb 21 at 13:52
add comment

2 Answers

up vote 2 down vote accepted

Yes, the large offset is a problem. MySQL does early row lookups, which do not allow throwing data away as easy as one imagines. MySQL retrieves the whole records as it processes the database and each record may be of variable length. Throwing rows away afterwards becomes as cumbersome as throwing each single row away. It's not like "throw away x*y bytes".

There are tricks to force MySQL into late row lookup, which means process the index and do all operations concerning that, than retrieve the row. This offers the possibility to throw rows away much faster.

See

share|improve this answer
add comment

It's probably because the reverse scanning of an index can be slower that forward scanning, as explained, for example, here: http://www.mysqlperformanceblog.com/2006/05/09/descending-indexing-and-loose-index-scan/

share|improve this answer
    
I am not sure if descending index would help because it takes the same 0.00sec if I do SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id DESC LIMIT 20,5. The offset seems more important than the direction. –  Question Overflow Feb 21 at 13:23
    
Yes, the issue is the large offset, not the index direction. –  ypercube Feb 21 at 13:24
    
I wonder what would be the timing for SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 499975,5 –  mustaccio Feb 21 at 17:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.