SQL Index Performance - ASC vs DESC
indexing, mysql, primary-key, sql
Solution
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.
Problem
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?