Different Orders of Composite Index Fields in TiDB Causing Full Index Scan

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TIDB 复合索引字段order 不同顺序造成全索引遍历

| username: TiDBer_SkVOyOMj

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.1.4
[Reproduction Path]
[Encountered Problem: Problem Phenomenon and Impact]
When using the composite index (key+updated_at) for search, the following search statement is used:

select `key`, updated_at, is_delete from table order by `key` ASC, updated_at DESC

A full index scan occurs

causing the query to be very slow.

If both order conditions use the same sorting direction, it does not happen:

select `key`, updated_at, is_delete from table order by `key` ASC, updated_at ASC 
or
select `key`, updated_at, is_delete from table order by `key` DESC, updated_at DESC 

| username: weixiaobing | Original post link

Because the index itself is ordered and is created as ASC by default. Now the sorting requirements for the two fields are different, so a full scan is needed to return the results.

| username: TiDBer_SkVOyOMj | Original post link

Is there any way to optimize this from TiDB’s perspective? Here, the key query is prioritized, and a limit is added.

| username: 人如其名 | Original post link

There is no way to optimize it. One of the prerequisites for keeping order is that the sorting of all sorting fields must be consistent.

| username: tidb菜鸟一只 | Original post link

You can try creating the index using the following method: CREATE INDEX idx_ab ON t (a ASC, b DESC);

| username: weixiaobing | Original post link

TiDB does not support descending indexes. The index can be created successfully, but you can see from the “show create table” command that it is actually still ASC. MySQL also only started supporting descending indexes in version 8.0.

| username: tidb菜鸟一只 | Original post link

We can only wait for TiDB to support it in the future.

| username: 特雷西-迈克-格雷迪 | Original post link

As of version 6.5, TiDB does not support descending indexes (similar to MySQL 5.7). MySQL 8.0 introduced descending indexes. The descending index mentioned here refers to composite indexes; for single-column indexes, both desc and asc are the same.

| username: 海石花47 | Original post link

Got it, McGrady.

| username: liuis | Original post link

Please provide the Chinese text you would like translated to English.

| username: 特雷西-迈克-格雷迪 | Original post link

Oh wow :100: