I'm trying to learn the 'order by optimization' of mysql, so i search the related topic and find the official doc of mysql8 says :

A condition for index use is that the index must have the same homogeneity, but need not have the same actual direction.

If a query mixes ASC and DESC, the optimizer can use an index on the columns if the index also uses corresponding mixed ascending and descending columns:

SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 ASC;

The optimizer can use an index on (key_part1, key_part2) if key_part1 is descending and key_part2 is ascending. It can also use an index on those columns (with a backward scan) if key_part1 is ascending and key_part2 is descending.

When creating a multiple column index, take (key_part1, key_part2) for example, we can't specify the order for key_part1 and key_part2, when we execute the query above, how optimizer can know that the index have the same homogeneity or not?

According to the doc ,the counter intuitive is that even if we use different sort order for two columns(key_part1 desc and the key_part2 asc) when querying, mysql optimizer may still make use the index if the index have the same homogeneity 。

I searched everywhere but can't find the answer, i hope a further detailed explanation.

CodePudding user response：

we can't specify the order for key_part1 and key_part2

You can do something like the following to create a multiple column index with different sort orders:

CREATE INDEX idx ON table (col1 asc, col2 desc);