MySQL Descending Index

Descending Index

It is a type of index that stores key values in descending order. This query improves the performance of an index query. MySQL system displays the index as per column order in the table.

Suppose you want to display the index in order as per your requirement. The index order assigns either ascending or descending. You will use the "DESC" keyword for descending order. If you want your index in ascending order, then use the "ASC" keyword.

Syntax

The basic syntax of the descending index shows below.

INDEX key_name (column1 DESC, column DESC)

MySQL descending index shows below.

 CREATE TABLE table_name
 (Column1 INT NOT NULL,
 Column INT NOT NULL,
 INDEX key_name (column1 DESC, column DESC));
 MySQL ascending index shows below.
 CREATE TABLE table_name
 (Column1 INT NOT NULL,
 Column INT NOT NULL,
 INDEX key_name (column1 ASC, column ASC));
 MySQL descending and ascending index shows below.
 CREATE TABLE table_name
 (Column1 INT NOT NULL,
 Column INT NOT NULL,
 INDEX key_name (column1 DESC, column ASC)); 

Examples of the Descending index

1) Example: The basic descending index example shows below.

Execute the below query to get descending orders of the column. Here we will assign the DESC keyword to the phone and name column.

 mysql> create table index_table(
 roll_number INT NOT NULL PRIMARY KEY,
 phone INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 mark text NOT NULL,
 img blob NOT NULL,
 INDEX roll_number (phone DESC),
 INDEX name (name DESC),
 INDEX img (img (400) DESC)); 

OUTPUT

Execute the below query to get the required index and its information.

mysql> show index from index_table;

The above output image shows a similar index name for multiple columns. The collation shows the "D" value, which means descending order for the index.

2) Example: The multiple descending index example shows below.

Execute the below query to get descending orders of the column. All column uses descending order using the "DESC" keyword. The multiple indexes with several columns assign for descending index.

 mysql> create table index_table(
 roll_number INT NOT NULL PRIMARY KEY,
 phone INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 mark text NOT NULL,
 img blob NOT NULL,
 INDEX roll_number (roll_number DESC, phone DESC),
 INDEX name (name DESC, mark (100) DESC),
 INDEX img (img (400) DESC)); 

OUTPUT

Execute the below query to get the required index and its information.

mysql> show index from index_table;

The above output image shows a single index name for multiple columns. The collation shows the "D" value, which means descending order for the index.

3) Example: The basic ascending index example shows below.

Execute the below query to get descending orders of the column. All column uses order with the "ASC" and "DESC" keyword. The multiple indexes with several table columns assign for ascending and descending index.

 mysql> create table index_table(
 roll_number INT NOT NULL PRIMARY KEY,
 phone INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 mark text NOT NULL,
 img blob NOT NULL,
 INDEX roll_number (roll_number DESC, phone ASC),
 INDEX name (name DESC, mark(10) DESC),
 INDEX img (img(100) ASC)); 

OUTPUT

Execute the below query to get the required index and its information.

mysql> show index from index_table;

The above output image shows a single index name to multiple columns. The collation shows the "A" and "D" values where "A" displays an ascending order and "D" displays a descending order to the index. The complete table index assigns descending order.

4) Example: The descending and ascending index example shows below.

Execute the below query to get ascending orders of the column. All column uses ascending order using the "ASC" keyword. The multiple indexes with several table columns assign to ascending index.

 mysql> create table index_table(
 roll_number INT NOT NULL PRIMARY KEY,
 phone INT NOT NULL,
 name VARCHAR(10) NOT NULL,
 mark text NOT NULL,
 img blob NOT NULL,
 INDEX roll_number (roll_number ASC, phone ASC),
 INDEX name (name ASC, mark(10) ASC),
 INDEX img (img(100) ASC)); 

OUTPUT

Execute the below query to get the required index and its information.

mysql> show index from index_table;

The above output image shows a single index name for multiple columns. The collation shows the "A" value, which means ascending order for index. The complete table index assigns ascending order.