How can you view all indexes defined on a table?

Difficulty: 5/10

SHOW INDEX, information_schema.STATISTICS, index auditing

How to View All Indexes Defined on a Table in MySQL

MySQL provides several ways to list all indexes on a specific table. These commands help you inspect index names, columns, uniqueness, index types, and more.

1. Using SHOW INDEX

1
The most common method to view indexes.
2
Displays index name, columns, uniqueness, cardinality, and index type.

Syntax

2. Using INFORMATION_SCHEMA.STATISTICS

1
Provides detailed metadata about indexes.
2
Useful when you need to query/filter index information programmatically.

Query Example

3. Using DESCRIBE (Partial Info Only)

1
Shows PRIMARY KEY and UNIQUE constraints.
2
Does NOT list all secondary indexes.

Command

The recommended and most complete method is using SHOW INDEX or querying INFORMATION_SCHEMA.STATISTICS for more detailed control.

Scenario Questions

0-2 years experience

1You need to check which indexes exist on the `orders` table before adding a new one. How would you list all indexes defined on that table using MySQL?
2A teammate asks you to verify whether a composite index on `user_id` and `created_at` exists. What command would you run to see all indexes on the `user_activity` table?
3If you run `SHOW INDEX FROM products;` and see a row with `Key_name` = 'PRIMARY', what does that tell you about the table's indexes?

Follow-up Questions

What differences would you expect between using SHOW INDEX and querying information_schema?
How would you script this check for multiple tables in an automated deployment?
Can you think of any permissions or version constraints that might affect your ability to view indexes?