SQL Index Performance - ASC vs DESC

Question

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?

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

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