Sql query performance difference between limit row count

Question

I have a hug table with 170,000 records.

What is difference between this query

Showing rows 0 - 299 (1,422 total, Query took 1.9008 sec)
    SELECT 1 FROM `p_apartmentbuy` p 
    where 
    p.price between 500000000 and 900000000
    and p.yard = 1
    and p.dateadd between 1290000000 and 1320000000
    ORDER BY `p`.`id` desc
    limit 1669

Explain

And this one:

Showing rows 0 - 299 (1,422 total, Query took 0.2625 sec)
    SELECT 1 FROM `p_apartmentbuy` p 
    where 
    p.price between 500000000 and 900000000
    and p.yard = 1
    and p.dateadd between 1290000000 and 1320000000
    ORDER BY `p`.`id` desc
    limit 1670

Explain:

Both of these queries are using 1 table with same data and have same where clasue, but only limit row count are different

Answer 1

MySQL has a buffer for sorting. When the stuff to be sorted is too big, it sorts chunks, then mergesorts them. This is called "filesort". Your 1670-th row apparently just overflows the sort buffer.

Read more details here.

Now why it picks another key for the in-memory sort... I am not too sure; but apparently its strategy is not quite good since it ends up being slower.

Answer 2

recap: odd that the query returning more rows runs much faster

this is not related to buffer vs file sort, sorting 1400 records takes well under 1 second

the first explain shows the query optimizer doing a linear scan, the second explain shows it using an index. Even a partially helpful index is usually much better than none at all.

Internally, mysql maintains stats about the size of indexes and tries to guess which index, or whether a linear scan would be faster. This estimate is data specific, I've seen mysql use the right index 99 times out of 100, but every now and then pick a different one and run the query 50x slower.

You can override the built-in query optimizer and specify the index to use manually, with SELECT ... FROM ... FORCE INDEX (...)