I am currently optimizing a slow-performing web application and I suspect that some of my larger tables are missing necessary indexes. I need to verify which indexes already exist for a specific table to avoid creating duplicates. Is there a single SQL command that provides a detailed breakdown of index names, column names, and unique constraints? Additionally, if I want to see every index across an entire database to perform a full audit, how would I query the information_schema to get that data in one view?
3 answers
To see the indexes for a specific table, the most common and easiest command is SHOW INDEX FROM table_name;. This will return a result set containing the Table name, Non_unique (0 for unique, 1 for non-unique), Key_name (index name), Seq_in_index (column order), and the Column_name itself. If you want a more readable version that shows the "Create Table" syntax including all index definitions, you can use SHOW CREATE TABLE table_name;. For a database-wide audit, you'll need to query the STATISTICS table in the information_schema. Running SELECT * FROM information_schema.statistics WHERE table_schema = 'your_db_name'; will give you a comprehensive list of every index across all tables in that specific database.
When you run the SHOW INDEX command, do you know how to interpret the "Cardinality" column? I've noticed it sometimes shows null or very low numbers—does that mean the index is actually being ignored by the MySQL optimizer during query execution?
ou can also use the DESCRIBE table_name; command. While it doesn't give as much detail as SHOW INDEX, it quickly shows which columns have a "PRI", "UNI", or "MUL" key status.
I agree with Nancy. DESCRIBE is my go-to for a quick glance, but for a deep dive into performance tuning, nothing beats querying the information_schema directly like Patricia suggested.
Steven, that is a great observation! Cardinality represents the number of unique values in the index. If it's low, the optimizer might indeed skip the index and perform a full table scan instead. To fix "stale" cardinality values, you should run ANALYZE TABLE table_name;. This refreshes the index statistics without locking the table for too long. In my experience with high-traffic Software Development projects, keeping these statistics updated is just as important as creating the indexes themselves to ensure the query planner makes the right decisions.