Why the order of indexed columns in ORDER BY affect performance?

Question

I'm trying to improve performance of the following query which takes a 1+ minutes to execute:

SELECT *
FROM   test
WHERE  ( created_at < '2023-3-31 06:10:20.871' )
       AND ( ( id > '2a95048f' )
              OR ( id = '2a95048f'
                   AND created_at > '2022-12-27 23:53:24.958' ) )
ORDER  BY id ASC,
          created_at ASC
LIMIT  1000; 

I altered the query by switching the order in the ORDER BY and the query returned results in 549ms:

SELECT *
FROM   test
WHERE  ( created_at < '2023-3-31 06:10:20.871' )
       AND ( ( id > '2a95048f' )
              OR ( id = '2a95048f'
                   AND created_at > '2022-12-27 23:53:24.958' ) )
ORDER  BY created_at ASC,
          id ASC
LIMIT  1000; 

Here is some information about the indexes:

• ID - Cardinality 6.8 million, unique
• CREATED_AT - Cardinality 7.1 million, NOT unique

Why is one drastically performant than the other?

UPDATE - "SHOW INDEX FROM TEST"

Answer 1

As Andrea mentioned, please add your index definitions to your Post, for performance related questions.

But assuming Andrea is correct, and your index is defined on (created_at, id) then the answer your question of "Why the order of indexed columns in ORDER BY affect performance?" is because the order that the columns are specified is the order of which the data is sorted - and if those orderings don't match between your ORDER BY clause and the index definition, then the index can't be used to eliminate a sort operation for your query.

By defining an index on (created_at, id), you're telling the index to first sort the data by created_at and then secondly sort it by id within the original sorting.

So with an index definition, think of it like a phone book. The phone book usually sorts by LastName first, and then FirstName second. If your ORDER BY clause wanted to order the data in that phone book by the LastName, FirstName then no work needs to be done because the phone book is already pre-sorted that way (i.e. the index already stores the data that way). But if you wanted to ORDER BY the FirstName, LastName against the phone book, then the entire phone book needs to be re-sorted to accomplish that (i.e. your index on (LastName, FirstName) can't help you here).

Answer 2

To get a more precise answer you should post definition of indexes on your table and the query plan of both query.

But, if I had to make a guess, I'd say that the performace difference is probably due to the combination of ORDER BY and LIMIT.

You probably have an index on (created_at, id) but not one on (id, created_at)

If you have an index that covers the order by columns in the same order, mysql can search the table using the index, get the rows in the needed order and stop after it finds 1000 rows that satisfy the where condition.

If there is no such index, mysql has to select all rows that satisfy the where condition (probably many thousands), then sort them and only after the sort it can keep the first 1000 and drop the rest.