SQL Index Performance - ASC vs DESC

mysql sql indexing primary-key

21,736

Solution 1

Updated Answer for MySQL 8.0

As noted by Kazimieras Aliulis in the comments, support for descending indexes is being added in MySQL 8.0:

MySQL supports descending indexes: DESC in an index definition is no longer ignored but causes storage of key values in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others.

Original Answer for Earlier Versions

DESC indexing is not currently implemented in MySQL... the engine ignores the provided sort and always uses ASC:

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

For another RBDMS that does implement this feature, such as SQL Server, the DESC specification is only beneficial when sorting by compound indexes... and won't have an impact on the lookup time for newly created users versus older users.

Solution 2

From the MySQL 5.6 documentation:

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

Solution 3

Someday I've been given by simple yet brilliant trick, how to make a descending index for mysql: Just by adding another column with negative (mirror value). Say, for the unsigned int it would be just value*-1 - so, it works for the unix timestamps.
For varchars the idea is similar but implementation is a bit more complex.

21,736

pjama

Updated on July 09, 2022

Comments

pjama almost 2 years
I've got a user table keyed on an auto-increment int column that looks something like this:
```
CREATE TABLE `user_def` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) NOT NULL,
  `date_created` datetime NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name_UNIQUE` (`user_name`),
) ENGINE=MyISAM
```
Are there any practical performance advantages to using a DESC index (primary key) rather than the default ASC?

My suspicion / reasoning is as follows: I'm assuming that more recent users are going to be more active (i.e. accessing the table more often), therefore making the index more efficient.

Is my understanding correct?
- Albin Sunnanbo about 12 years
  
  DESC would fragment your index a lot when using AUTO_INCREMENT
- Azzy over 11 years
  
  sort of repeating question... check here: stackoverflow.com/questions/743858/…
- John Dvorak over 11 years
  
  I don't think there is any difference. The only thing that matters is the relative sorting order between columns, and only for the purpose of range queries.
dsjoerg over 11 years

That is not entirely true. For compound indexes, it would be useful. For example I would like to define a compound index on col_a ASC, col_b DESC because that's how I will be sorting in my queries. Unfortunately MySQL doesn't support this, and so my queries cannot use an index.
John Dvorak over 11 years

Then how is ORDER BY col1 ASC, col2 DESC computed from the col1, col2 index?
AndreKR over 11 years

It isn't, the index isn't used for that. From dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html: In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following: [...] You mix ASC and DESC
John Dvorak over 11 years

So it's not possible to create an index for ORDER BY col1 ASC, col2 DESC in MySQL?
epeleg over 11 years

So It sounds as if it means that if I have a table with an int field with 10 000 000 ids and it has a unique index on this field then select * from table where id=1 would resolve somewhat faster then select * from table where id=10000000 ?
AndreKR over 11 years

Maybe theoretically but I haven't found any practical performance difference between the index being read forwards or backwards. Actually I tried it right now and repetetively found ORDER BY pk DESC LIMIT 1 faster than ORDER BY pk ASC LIMIT 1, although that "difference" was well inside the margin of error.
Mihai Danila almost 11 years

Not for this guy it wasn't the same. Compare half a second to 32 seconds: stackoverflow.com/questions/15017950/…
AndreKR almost 11 years

@MihaiDanila The question you linked has nothing to do with ordering and also nothing about 32 seconds. Did you put the wrong link?
Mihai Danila almost 11 years

Try this one: stackoverflow.com/questions/2886575/…
AndreKR almost 11 years

That case is rather special as he forces MySQL to use an index that it normally would not have used and combines that with LIMIT.
Colandus almost 10 years

@Your Common Sense Hey! I am in need of indexing a query that is using a descending sort. Would you mind explain this trick? Didn't quite get it :(
Your Common Sense almost 10 years

@Colandus make a column where value is multiplied by -1. I.e. for the value 1, new column would be -1, for 10 it would be -10 and so on. Then add index for this new column. Sort it by ASC and it will sort as tough by old column desc
Colandus almost 10 years

@Your Common Sense Ah that's smart, thanks for sharing.
Code Commander over 7 years

This is an old post, but in case anyone is curious, DESC indexing is still not implemented as of MySQL 5.7 according to the latest documentation
Paladini about 7 years

Thank you for the update, @CodeCommander ! I think the answer from Michael Fredrickson should be updated to contain this update.
Kazimieras Aliulis about 7 years

Mysql 8 going to have desc indexes: dev.mysql.com/doc/refman/8.0/en/descending-indexes.html