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

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"

• I'm confused why someone would downvote this post if it's an original question. It's not a duplicate, there was enough information for everyone to answer.
– CKT
Apr 1 at 12:56

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).

• I've added the index definitions.
– CKT
Mar 31 at 14:55
• @CKT I'm not really sure how to read what you've added as the index definitions (but that may just be a lack of my familiarity with that resultset in MySQL). In any case, does my answer make sense to you and helps clarify your question?
– J.D.
Mar 31 at 15:17

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.

• Hi! I've added the index definitions.
– CKT
Mar 31 at 14:55