How can you view all indexes defined on a table?

0-2 years experience

1. 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?
2. 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?
3. 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?

2-5 years experience

1. 1During a performance investigation you notice a query on `sales` is doing full table scans. How would you retrieve the current indexes on `sales` and use that information to decide if an index is missing?
2. 2Your migration script fails because it tries to drop an index that doesn't exist. Walk me through how you'd programmatically verify the presence of that index before attempting to drop it.
3. 3You have a read replica that reports a different execution plan than the primary. How could you compare the indexes on the same table across both instances to troubleshoot?

5-8 years experience

1. 1We are planning to shard the `events` table and need to ensure index definitions are consistent across shards. Describe the process you’d use to audit all indexes on each shard and enforce uniformity.
2. 2A recent schema change introduced a duplicate index, causing write amplification. How would you identify all indexes on the affected table, assess redundancy, and safely remove the extra index without downtime?
3. 3Our monitoring alerts show increasing latency on a high‑traffic table. Explain how you would use MySQL metadata to list indexes, evaluate their selectivity, and decide whether to add, drop, or rebuild indexes at scale.

8+ years experience

1. 1Our organization is moving from MySQL to a distributed SQL platform. As part of the migration, you need to capture the complete index definition set for hundreds of tables and translate them. How would you design a solution to extract, version, and validate index metadata across services?
2. 2Multiple teams maintain overlapping micro‑services that each create their own indexes on shared tables, leading to index bloat. Propose a governance model and tooling approach to centrally audit and standardize indexes, including how you’d surface the current index list to developers.
3. 3We want to implement automated CI checks that fail if a pull request adds an index without a documented justification. Outline how you’d integrate a step that queries MySQL to list existing indexes and compare against the proposed schema changes.