CODEWITHSUNDEEP
mysqlindexing
hiro
hiro

Reputation: 271

Order by Primary Key and Index

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

Upvotes: 2

Views: 4246

Answers (3)

Nouaman Harti
Nouaman Harti

Reputation: 223

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?

Upvotes: 2

georgecj11
georgecj11

Reputation: 1637

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.

Upvotes: 2

fthiella
fthiella

Reputation: 49049

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.

Upvotes: 0

Related Questions