Sign in

On the relationship between ascending and descending order and order by of joint index and its invalidation

mantas edited in Thu, 23 Jun 2022

MySQL: I have seen a saying before that if the items after order by are not sorted consistently, it will lead to the failure of the joint index, such as

 order by a ASC, b DESC, c DESC  

A ascending, B descending, sort inconsistent, index (a) , b , c) Invalidation

But you can also specify sort when building an index. If you build an index (a ASC, B DESC, C DESC), is the order by a ASC, B DESC, C desc statement index still invalid?

1 Replies
commented on Thu, 23 Jun 2022

The description of ASC and desc in index definition in MySQL document

MySQL < 8.0

A key_ part 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.

MySQL >= 8.0

A key_ part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order . The default is ascending if no order specifier is given . ASC and DESC are not permitted for HASH indexes . As of MySQL 8 . 0 . twelve , ASC and DESC are not permitted for SPATIAL indexes .

Therefore, in versions before 8.0, desc is invalid, and the index (a ASC, B DESC, C DESC) is equal to (a ASC, B ASC, C ASC), so the entire joint index cannot be used for sorting.

eight . After 0, index descending is allowed. Regardless of SQL optimization and other details, as long as the order by order is consistent with the index order, index sorting can still be used.

(8.0)CREATE INDEX Syntax(5.7)CREATE INDEX Syntax

lock This question has been locked and the reply function has been disabled.