CODEWITHSUNDEEP
mysqlsqlindexingprimary-key
pjama
pjama

Reputation: 3044

SQL Index Performance - ASC vs DESC

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?

Upvotes: 24

Views: 25569

Answers (4)

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

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.

Upvotes: 28

Your Common Sense
Your Common Sense

Reputation: 157989

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.

Upvotes: 8

AndreKR
AndreKR

Reputation: 33697

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.

Upvotes: 10

Mushu
Mushu

Reputation: 271

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.

Upvotes: 0

Related Questions