Why it takes more time for MySQL to get records starting with higher number?

Question

Possible Duplicate:
How can I speed up a MySQL query with a large offset in the LIMIT clause?

In our application we are showing records from MySQL on a web page. Like in most such applications we use paging. So the query looks like this:

select * from sms_message 
where account_group_name = 'scott' 
and received_on > '2012-10-11' and 
received_on < '2012-11-30' 
order by received_on desc 
limit 200 offset 3000000;

This query takes 104 seconds. If I only change offset to low one or remove it completely, it's only half a second. Why is that?

There is only one compound index, and it's account_group_name, received_on and two other columns. Table is InnoDB.

UPDATE:

Explain returns:

1   SIMPLE  sms_message ref all_filters_index   all_filters_index   258 const   2190030 Using where

all_filters_index is 4-columns filter mentioned above.

I think it's because of the fact that somewhere down the road engine needs to count first 3000000 before populating next 100 results. — Vardan Gupta, Nov 09 '12 at 19:52
@MikeBrant I updated the question and attached explain output. — amorfis, Nov 09 '12 at 21:06

score 1 · Answer 1 · answered Nov 09 '12 at 19:57

Yes this is true, time increases as offset value increases and the reason is because offset works on the physical position of rows in the table which is not indexed. So to find a row at offset x, the database engine must iterate through all the rows from 0 to x.

Why it takes more time for MySQL to get records starting with higher number?

1 Answers1