MySQL Show Index
Before adding or dropping an index you need to know which indexes a table already has. SHOW INDEX lists them, one row per indexed column, with details such as uniqueness, column order and estimated cardinality.
1. Syntax
SHOW {INDEX | INDEXES | KEYS} {FROM | IN} table_name [{FROM | IN} db_name] [WHERE condition];SHOW INDEX FROM customers;Result: 7 rows
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Sub_part | Cardinality | Visible | Expression |
|---|---|---|---|---|---|---|---|---|
| customers | 0 | PRIMARY | 1 | id | NULL | 4916 | YES | NULL |
| customers | 0 | uq_email | 1 | NULL | 4916 | YES | NULL | |
| customers | 1 | idx_city_last | 1 | city | NULL | 12 | YES | NULL |
| customers | 1 | idx_city_last | 2 | last_name | NULL | 60 | YES | NULL |
| customers | 1 | idx_email_prefix | 1 | 8 | 4916 | YES | NULL | |
| customers | 1 | idx_spend | 1 | spend | NULL | 4916 | NO | NULL |
| customers | 1 | idx_signup_year | 1 | NULL | NULL | 5 | YES | year(`signup_on`) |
2. Understanding the Output
| Column | Meaning | In our example |
|---|---|---|
Key_name | Index name; PRIMARY for the primary key | idx_city_last appears twice: one row per column |
Non_unique | 0 = unique index, 1 = duplicates allowed | PRIMARY and uq_email are 0 |
Seq_in_index | Position of the column in the index (1, 2, …) | city = 1, last_name = 2 |
Column_name | Indexed column (NULL for functional indexes) | NULL for idx_signup_year |
Sub_part | Prefix length for prefix indexes | 8 for idx_email_prefix |
Cardinality | Estimated number of distinct values (refreshed by ANALYZE TABLE) | about 12 for city |
Null | YES if the column allows NULL | |
Index_type | BTREE, FULLTEXT, SPATIAL, HASH | BTREE |
Visible | NO for invisible indexes | NO for idx_spend |
Expression | Expression of a functional index | year(signup_on) |
3. Cardinality and Selectivity
Cardinality estimates how many different values an index holds. Divide by the row count to get selectivity: close to 1 means nearly unique (great for lookups), close to 0 means many duplicates (an index alone helps less).
SELECT index_name, column_name, cardinality,
ROUND(cardinality / (SELECT COUNT(*) FROM crm.customers), 4) AS selectivity
FROM information_schema.statistics
WHERE table_schema = 'crm' AND table_name = 'customers' AND seq_in_index = 1
ORDER BY selectivity DESC;Result: 6 rows
| INDEX_NAME | COLUMN_NAME | CARDINALITY | selectivity |
|---|---|---|---|
| idx_email_prefix | 4916 | 0.9832 | |
| idx_spend | spend | 4916 | 0.9832 |
| PRIMARY | id | 4916 | 0.9832 |
| uq_email | 4916 | 0.9832 | |
| idx_city_last | city | 12 | 0.0024 |
| idx_signup_year | NULL | 5 | 0.0010 |
Cardinality is an estimate from sampled pages. Run
ANALYZE TABLE after big data changes to refresh it.4. Filtering the Output
SHOW INDEX FROM customers WHERE Non_unique = 0;Unique indexes only
| Key_name | Column_name |
|---|---|
| PRIMARY | id |
| uq_email |
SHOW INDEX FROM customers WHERE Visible = 'NO';Invisible indexes
| Key_name | Column_name | Visible |
|---|---|---|
| idx_spend | spend | NO |
SHOW KEYS FROM customers FROM crm WHERE Key_name = 'idx_city_last';SHOW KEYS is a synonym
| Key_name | Seq_in_index | Column_name |
|---|---|---|
| idx_city_last | 1 | city |
| idx_city_last | 2 | last_name |
5. One Line per Index
SHOW INDEX returns one row per column. To see each index on one line, group information_schema.statistics:
SELECT index_name,
IF(MIN(non_unique) = 0, 'UNIQUE', '') AS uniq,
GROUP_CONCAT(COALESCE(column_name, expression) ORDER BY seq_in_index SEPARATOR ', ') AS columns,
MAX(is_visible) AS visible
FROM information_schema.statistics
WHERE table_schema = 'crm' AND table_name = 'customers'
GROUP BY index_name
ORDER BY index_name;Result: 6 rows
| INDEX_NAME | uniq | columns | visible |
|---|---|---|---|
| idx_city_last | city, last_name | YES | |
| idx_email_prefix | YES | ||
| idx_signup_year | year(`signup_on`) | YES | |
| idx_spend | spend | NO | |
| PRIMARY | UNIQUE | id | YES |
| uq_email | UNIQUE | YES |
6. Indexes Across a Whole Database
SELECT table_name, COUNT(DISTINCT index_name) AS indexes
FROM information_schema.statistics
WHERE table_schema = 'crm'
GROUP BY table_name;Result: 1 row
| TABLE_NAME | indexes |
|---|---|
| customers | 6 |
And tables without any secondary index, which are worth reviewing:
CREATE TABLE crm.notes (id INT PRIMARY KEY, customer_id INT, body TEXT);
SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_schema = 'crm' AND t.table_type = 'BASE TABLE'
AND NOT EXISTS (SELECT 1 FROM information_schema.statistics s
WHERE s.table_schema = t.table_schema AND s.table_name = t.table_name AND s.index_name <> 'PRIMARY');Result: 1 row
| TABLE_NAME |
|---|
| notes |
7. SHOW CREATE TABLE
To see index definitions as SQL (useful for copying), use SHOW CREATE TABLE:
SHOW CREATE TABLE notes;Result: 1 row
| Table | Create Table |
|---|---|
| notes | CREATE TABLE `notes` ( `id` int NOT NULL, `customer_id` int DEFAULT NULL, `body` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
8. Summary
SHOW INDEX FROM table;(or SHOW KEYS) lists indexes, one row per column.- Key_name, Non_unique, Seq_in_index, Cardinality and Visible are the most useful columns.
- High cardinality relative to rows means a selective, effective index.
information_schema.statisticslets you group, filter and search across tables.
Was this tutorial helpful?