How to index in descending order by mysql8.0

This article introduces the relevant knowledge of "how to index mysql8.0 in descending order". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

MySQL supports descending indexing: DESC is no longer ignored in the index definition, but causes keys to be stored in descending order.

And only the InnoDB storage engine supports descending indexing

Example: Creating a table

CREATE TABLE test.t (

id INT NOT NULL auto_increment PRIMARY KEY,c1 INT, c2 INT

);

insert data

INSERT into test.t (c1,c2) VALUES (1,2),(1,3),(1,4),(4,2),(4,1),(4,3),(2,1),(2,3),(2,4),(3,2),(3,1),(3,4);

View with sql optimizer

EXPLAIN select FROM test.t WHERE c1=1 ORDER BY c1 ASC, c2 ASC;

Create index sort

CREATE INDEX idx1 ON test.t(c1,c2);

View with sql optimizer

EXPLAIN select FROM test.t WHERE c1=1 ORDER BY c1 ASC, c2 ASC;

View with sql optimizer

EXPLAIN select FROM test.t WHERE c1=1 ORDER BY c1 ASC, c2 DESC;

Create index sort

CREATE INDEX idx1 ON test.t(c1,c2);

View with sql optimizer

EXPLAIN

SELECT FROM test.t ORDER BY c1 ASC,c2 DESC;

View table structure

SHOW CREATE TABLE t;

The content of "mysql8.0 how to index in descending order" is introduced here. Thank you for reading it. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!