23

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?

pjama
  • 3,014
  • 3
  • 26
  • 27
  • `DESC` would fragment your index a lot when using `AUTO_INCREMENT` – Albin Sunnanbo Apr 12 '12 at 19:26
  • 1
    sort of repeating question... check here: http://stackoverflow.com/questions/743858/sql-server-indexes-ascending-or-descending-what-difference-does-it-make – Azzy Dec 25 '12 at 13:00
  • 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. – John Dvorak Dec 25 '12 at 13:01

4 Answers4

28

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.

Michael Fredrickson
  • 36,839
  • 5
  • 92
  • 109
  • 2
    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](http://dev.mysql.com/doc/refman/5.7/en/create-index.html) – Code Commander Sep 01 '16 at 17:50
  • Thank you for the update, @CodeCommander ! I think the answer from Michael Fredrickson should be updated to contain this update. – Paladini Feb 09 '17 at 21:15
  • 1
    Mysql 8 going to have desc indexes: https://dev.mysql.com/doc/refman/8.0/en/descending-indexes.html – Kazimieras Aliulis Apr 26 '17 at 07:36
10

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.

AndreKR
  • 32,613
  • 18
  • 106
  • 168
  • 2
    Then how is `ORDER BY col1 ASC, col2 DESC` computed from the `col1, col2` index? – John Dvorak Dec 25 '12 at 13:05
  • 1
    It isn't, the index isn't used for that. From http://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 – AndreKR Dec 25 '12 at 13:09
  • 1
    So it's not possible to create an index for `ORDER BY col1 ASC, col2 DESC` in MySQL? – John Dvorak Dec 25 '12 at 13:11
  • 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` ? – epeleg Dec 26 '12 at 09:05
  • 1
    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. – AndreKR Dec 26 '12 at 16:09
  • Not for this guy it wasn't the same. Compare half a second to 32 seconds: http://stackoverflow.com/questions/15017950/optimizing-less-than-greater-than-query-of-mysql – Mihai Danila Jul 23 '13 at 18:29
  • @MihaiDanila The question you linked has nothing to do with ordering and also nothing about 32 seconds. Did you put the wrong link? – AndreKR Jul 23 '13 at 21:56
  • Try this one: http://stackoverflow.com/questions/2886575/mysql-order-by-desc-is-fast-but-asc-is-very-slow – Mihai Danila Jul 24 '13 at 04:40
  • 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. – AndreKR Jul 24 '13 at 08:18
8

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.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • @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 :( – Colandus May 12 '14 at 17:13
  • @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 – Your Common Sense May 12 '14 at 17:24
  • @Your Common Sense Ah that's smart, thanks for sharing. – Colandus May 12 '14 at 17:47
0

In MySQL defining ASC or DESC for indexes is not only unsupported, but it would also be pointless. MySQL can traverse indexes in both directions as needed, so it does not require the order to be defined explicitly.

Mushu
  • 271
  • 1
  • 5
  • 2
    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. – dsjoerg Dec 16 '12 at 05:30