MySQL Show index

This query helps to access and retrieve table information. This syntax displays the required index or all indexes of the table. It also displays the index type as per the storage engine.

Syntax

The basic syntax of the show index is given below.

SHOW INDEX from table name;

The show index syntax with database name is shown below.

SHOW INDEX FROM table name IN database name;

The show index syntax with database name is shown below.

SHOW INDEX IN table name FROM database name;

The show index syntax with database name is shown below.

SHOW INDEX FROM database.table;

The show index syntax with the WHERE clause is shown below.

SHOW INDEX from table name WHERE condition;

The show index syntax using the keys clause is shown below.

SHOW KEYS from table name IN database name;

Examples of the show index

1) Example: The basic show index example is shown below:

Execute the below query to see table indexes and their information.

mysql> SHOW indexes FROM index_table;

OUTPUT

The above output image shows indexes from the table. The image shows the table name, unique key, key name, column name, index type, and table visibility.

2) Example: The basic show index with database example is shown below:

Execute the show indexes statement, including database and table name. You will use the required table name from a specific table name.The "show indexes" work with IN and FROM clauses.

mysql> SHOW indexes FROM index_table IN tutorial;

OUTPUT

3) Example: The basic show index example with database name is shown below.

Execute the show indexes statement using database and table name.

mysql> SHOW indexes IN index_table FROM tutorial;

OUTPUT

The above image shows indexes from the table. The image shows the table name, unique key, index name, column name, index type, collation, and table visibility.

4) Example: The basic show index example with database name is shown below.

Execute the show indexes statement with database and table name. Here we will use the required table name. The "show indexes" works with FROM clause and dot (.) symbol.

mysql> SHOW indexes FROM tutorial. index_table;

OUTPUT

The above output image shows indexes from the table. The image shows the table name, unique key, sequence of the index, index name, column name, index type, and table visibility.

5) Example: The basic show index example with the WHERE clause is shown below.

Execute show indexes statement with WHERE clause. Here, you will use conditions on the index column. The condition applies to the visible column. The visible column assigns the "YES" condition.

mysql> SHOW indexes IN index_table FROM tutorial where visible = 'YES';

OUTPUT

The above image shows indexes from the table. The output image shows the table name, sequence of the index, index name, column name, index type, and table visibility. The visible apply to the "YES" condition.

6) Example: The basic show index with key example is shown below.

Execute the following query to get the key name of the table. Here we will use the "keys" keyword instead of the index keyword.

mysql> SHOW keys FROM index_table IN tutorial;

OUTPUT

The above output image shows indexes from the table. The image shows the table name, unique key, sequence of the index, key name, column name, index type, and table visibility. The key name is known as an index column.