Limit with offset ("LIMIT 500000, 10") is slow even after index?

Question

I've got a table with int field, let's call it createTime. The table consists of few million records. Now I want to run the query:

select * from `table` order by `createTime` desc limit 500000, 10

I've created an index for createTime, but the query runs INCREDIBLY slow. What's the reason? How can I improve it?

Here's what EXPLAIN says:

id 1
select_type simple
table table
type index
possible_keys null
key createTime
key_len 4
ref null
rows 500010
extra

As for the offset, it's working much faster when it's small.

Answer 1

General rule: avoid OFFSET for large tables.

[A]s the offset increases, the time taken for the query to execute progressively increases, which can mean processing very large tables will take an extremely long time. 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.

The general rule of thumb is “never use offset in a limit clause”. For small tables you probably won’t notice any difference, but with tables with over a million rows you’re going to see huge performance increases.