How to Show Indexes of a Table in MySQL

To show indexes of a table in MySQL, use the SHOW INDEX FROM table_name; or SHOW KEYS FROM table_name; command. These commands list all indexes, including primary keys and unique indexes, with details like column names and index types.

Syntax

The basic syntax to display indexes of a table is:

• SHOW INDEX FROM table_name; - Lists all indexes on the specified table.
• SHOW KEYS FROM table_name; - Works the same as SHOW INDEX, showing keys and indexes.

Replace table_name with the actual name of your table.

sql

SHOW INDEX FROM table_name;

Example

This example shows how to list indexes of a table named employees. It demonstrates the output columns that describe each index.

sql

SHOW INDEX FROM employees;

Output

Table: employees
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: emp_id
Collation: A
Cardinality: 1000
Sub_part: NULL
Packed: NULL
Null: 
Index_type: BTREE
Comment: 
Index_comment: 

Common Pitfalls

Common mistakes when showing indexes include:

• Using the wrong table name or misspelling it, which causes an error or empty result.
• Confusing SHOW INDEX with SHOW TABLES or other commands that do not list indexes.
• Expecting only primary keys; remember that SHOW INDEX shows all indexes including unique and non-unique.

Always verify the table name and use the correct command to see indexes.

sql

/* Wrong: Using SHOW TABLES to see indexes */
SHOW TABLES;

/* Right: Use SHOW INDEX to see indexes */
SHOW INDEX FROM employees;

Quick Reference

Command	Description
SHOW INDEX FROM table_name;	Lists all indexes of the specified table.
SHOW KEYS FROM table_name;	Alias for SHOW INDEX, shows all keys and indexes.
DESCRIBE table_name;	Shows table columns but not indexes.
EXPLAIN SELECT ...	Shows query plan, including index usage but not index list.

Key Takeaways

Use SHOW INDEX FROM table_name; to list all indexes of a MySQL table.

SHOW KEYS FROM table_name; is an alias and works the same as SHOW INDEX.

Make sure to use the correct table name to avoid errors or empty results.

SHOW INDEX shows primary, unique, and non-unique indexes with details.

Do not confuse SHOW INDEX with commands that list tables or columns only.