Order by Primary Key and Index

Question

For many SQL queries, the "order by id desc" is a required component to get the latest rows. The general query will be something like

select * from table X where some_condition order by id desc

In general, does the "order by id" slow down the query very much?

Specifically, for query like:

select * from table X where some_col = some_value order by id desc

Do we really need to add index (some_col, id) to speed up the query?

In all cases, it is assumed that ID is auto incremental.

My confusion comes from the following guideline about order-by optimization:

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Since you are asking about performance [this SO discussion](http://stackoverflow.com/questions/426731/min-max-vs-order-by-and-limit) will give you a better understanding on using min/max, index and talks about limit as well. — von v., Mar 17 '13 at 17:08
Your link is very helpful, but it talked specifically about aggregated query vs order-by, and not so much related to my question. — hiro, Mar 17 '13 at 17:15
You asked about speeding up using an index and mentioned order by. If you read the accepted answer it can give you a lot actually and can help you think how you can implement a solution. Don't want to duplicate here what has been explained there already. — von v., Mar 17 '13 at 17:20

score 2 · Answer 1 · answered Mar 17 '13 at 16:52

2

select * from table X where some_col = some_value order by id desc

In queries like this, you need an index on some_col for sure. According to me index on (some_col) should be good enough over (some_col, id).

MySQL merge index should help you to use the index on id for sorting.

Also (id,some_Col) index will never be used when you search on some_col, while (some_col, id) will be.

answered Mar 17 '13 at 16:52

georgecj11

1,600
15
22

I used to create index on some_col (as you said "for sure"). But after reading the article: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html, I guess that a single index on some_col is not good enough. But it's still confusing because the key in "order by" is primary key. – hiro Mar 17 '13 at 16:58
Can you pls mention which one is confusing you. The below query is said to use the index. SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; – georgecj11 Mar 17 '13 at 17:07
I confuse because in order to achieve the best performance (for queries as I posted), the key should be (key_part1, key_part2) -- or (some_col, id). But a multiple index like that is pretty weird. – hiro Mar 17 '13 at 17:10
I believe the concept of merge index optimizations shld come to play here. Though I am not sure abt it. – georgecj11 Mar 17 '13 at 17:13

score 2 · Answer 2 · answered Nov 23 '15 at 12:52

Index on (some_col) is enough because MySql add the primary key as part of the index in innodb tables. So implicitly you will have (some_col, id) as index. You can check this article for more info : Can MySQL use primary key values from a secondary index?

score 0 · Answer 3 · answered Mar 17 '13 at 16:51

To get the first row you could also use this:

SELECT *
FROM tableX
WHERE id = (SELECT MIN(id)
            FROM tableX
            WHERE some conditions)

and this for the last:

SELECT *
FROM tableX
WHERE id = (SELECT MAX(id)
            FROM tableX
            WHERE some conditions)

but of course yes, an index on id and an index on some_col will be very useful to speed up the query.

Order by Primary Key and Index

3 Answers3