MySQL SHOW INDEX

The MySQL SHOW INDEX statement is used to display information about the indexes defined on a table. It helps you view the indexes, their names, the columns they contain and other details.

• Helps identify PRIMARY KEY, UNIQUE and other indexes.
• Useful for checking the index structure of a table.
• Helps in understanding and managing table indexes.

Syntax

SHOW INDEX FROM table_name;

Working

Consider a students table:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

To display the indexes of the students table:

SHOW INDEX FROM students;

Output:

• PRIMARY indicates the index created for the PRIMARY KEY.
• email indicates the index created for the UNIQUE constraint.
• Column_name shows the column on which the index is defined.
• Non_unique = 0 means the index does not allow duplicate values.

SHOW INDEX with WHERE Clause

SHOW INDEX FROM students
WHERE Key_name = 'PRIMARY';

• This displays information only about the PRIMARY index.

SHOW INDEX Using IN

The IN clause can be used to specify the database:

SHOW INDEX FROM students
IN mydatabase;

• This displays the indexes of the students table from the specified database.