How to Show Indexes in MySQL

The output has one row per indexed column, so a three-column composite index appears as three rows sharing a Key_name and distinguished by Seq_in_index. Non_unique is 0 for unique indexes. Cardinality is an estimate from the last ANALYZE TABLE and can be badly stale on a table that changes a lot.

SHOW INDEX FROM orders;

One row per index

Aggregating information_schema.statistics collapses each index to a single row with its columns in order, which is far easier to scan when a table has many indexes. It also works across tables and databases, so dropping the table_name filter lets you audit an entire schema at once.

SELECT index_name,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns,
       MAX(non_unique) AS non_unique
FROM information_schema.statistics
WHERE table_schema = 'shop' AND table_name = 'orders'
GROUP BY index_name;