How do I see all the indexes on a MySQL table?

Run SHOW INDEX FROM table_name, which lists every index defined on that table along with details like the index name, covered column, and uniqueness.

How do I remove an index in MySQL?

Use DROP INDEX index_name ON table_name, which removes the specified index without affecting any of the table's actual stored data.

Does dropping an index delete any data?

No, an index is a separate structure used purely for performance optimization, so dropping it has no effect whatsoever on the table's underlying data.

Why would I want to remove an existing index?

Unused indexes still add overhead to every INSERT, UPDATE, and DELETE affecting their column, and consume storage space, so removing genuinely unused indexes improves write performance without losing needed read performance.

How can I tell if a composite index's columns are in the order I expect?

The Seq_in_index column in SHOW INDEX output shows each column's position within a composite index, letting you confirm the actual defined column order.

How do I see all the indexes on a MySQL table?

Run SHOW INDEX FROM table_name, which lists every index defined on that table along with details like the index name, covered column, and uniqueness.

How do I remove an index in MySQL?

Use DROP INDEX index_name ON table_name, which removes the specified index without affecting any of the table's actual stored data.

Does dropping an index delete any data?

No, an index is a separate structure used purely for performance optimization, so dropping it has no effect whatsoever on the table's underlying data.

Why would I want to remove an existing index?

Unused indexes still add overhead to every INSERT, UPDATE, and DELETE affecting their column, and consume storage space, so removing genuinely unused indexes improves write performance without losing needed read performance.

How can I tell if a composite index's columns are in the order I expect?

The Seq_in_index column in SHOW INDEX output shows each column's position within a composite index, letting you confirm the actual defined column order.

SHOW INDEX and DROP INDEX in MySQL

Just as tables and views need ongoing management, indexes accumulate over a database's lifetime and need periodic review — SHOW INDEX reveals exactly what indexes currently exist on a table, and DROP INDEX removes ones that are no longer needed, which matters because every unnecessary index still carries the write-performance overhead covered in Lesson 9.7.

Key Definitions

SHOW INDEX: A MySQL statement that displays detailed information about all indexes defined on a specified table.
DROP INDEX: A statement that removes a specified index from a table, without affecting the table's actual data.

What You'll Learn

Use SHOW INDEX to inspect all indexes on a given table.
Interpret the key columns of SHOW INDEX output.
Use DROP INDEX to remove an index that is no longer needed.
Understand why regularly reviewing indexes is a valuable maintenance practice.

Detailed Explanation

Running `SHOW INDEX FROM doctors` returns a row for each index (or each column within a composite index) defined on the doctors table, including columns like Key_name (the index's name), Column_name (which column it covers), Non_unique (0 for unique indexes, 1 for non-unique), and Seq_in_index (the column's position within a composite index, useful for confirming column order). This is the standard way to audit exactly what indexes already exist on a table before deciding whether a new one is genuinely needed, or whether an existing one might be redundant.

Removing an index uses `DROP INDEX index_name ON table_name`. Unlike dropping a table or a view, dropping an index has zero impact on the actual data stored in the table — it only removes the separate performance-optimization structure, meaning queries that relied on that index will simply fall back to a full table scan (or another available index) going forward.

Regularly reviewing indexes with SHOW INDEX and removing genuinely unused ones with DROP INDEX is a valuable, often-overlooked maintenance practice. Indexes created early in a project for a query pattern that later changed or was removed entirely continue to silently consume write performance and storage space indefinitely unless someone actively audits and removes them — Lesson 9.11 explores this write-performance cost in more depth.

Visual Summary

Two command boxes. Left: [SHOW INDEX FROM doctors;] --> arrow to a sample result table showing columns [Key_name | Column_name | Non_unique | Seq_in_index]. Right: [DROP INDEX idx_doctors_salary ON doctors;] --> arrow to [Index structure removed; doctors table DATA unaffected].

Quick Reference

SHOW INDEX Column	Meaning
Key_name	The name of the index
Column_name	Which column this row of output refers to
Non_unique	0 if the index enforces uniqueness, 1 if it does not
Seq_in_index	The column's position within a composite index (1, 2, 3...)

SQL Example


CREATE TABLE departments (
  department_id   INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctors (
  doctor_id       INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name     VARCHAR(100) NOT NULL,
  department_id   INT,
  salary          INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE patients (
  patient_id      INT PRIMARY KEY AUTO_INCREMENT,
  patient_name    VARCHAR(100) NOT NULL,
  city            VARCHAR(80)
);

CREATE TABLE appointments (
  appointment_id    INT PRIMARY KEY AUTO_INCREMENT,
  patient_id        INT,
  doctor_id         INT,
  appointment_date  DATE,
  status            VARCHAR(20),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id)  REFERENCES doctors(doctor_id)
);

CREATE TABLE bills (
  bill_id         INT PRIMARY KEY AUTO_INCREMENT,
  appointment_id  INT,
  amount          DECIMAL(10,2),
  paid            BOOLEAN DEFAULT FALSE,
  FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id)
);

INSERT INTO departments VALUES
  (1, 'Cardiology'), (2, 'Orthopedics'), (3, 'Neurology'), (4, 'Dermatology');

INSERT INTO doctors (doctor_id, doctor_name, department_id, salary) VALUES
  (101, 'Dr. Verma', 1, 95000), (102, 'Dr. Iyer', 2, 78000),
  (103, 'Dr. Sen', 3, 120000), (104, 'Dr. Khan', 1, 88000);

INSERT INTO patients VALUES
  (201, 'Amit Rao', 'Pune'), (202, 'Neha Joshi', 'Mumbai'),
  (203, 'Karan Mehta', 'Delhi'), (204, 'Divya Nair', 'Pune');

INSERT INTO appointments VALUES
  (301, 201, 101, '2026-05-01', 'Completed'),
  (302, 202, 102, '2026-05-02', 'Completed'),
  (303, 203, 101, '2026-05-03', 'Cancelled'),
  (304, 204, 103, '2026-05-04', 'Completed');

INSERT INTO bills (appointment_id, amount, paid) VALUES
  (301, 1500.00, TRUE), (302, 2200.00, FALSE),
  (303, 800.00, TRUE), (304, 3000.00, TRUE);

CREATE INDEX idx_doctors_salary ON doctors(salary);
CREATE INDEX idx_doctors_dept_name ON doctors(department_id, doctor_name);

-- Inspect all indexes currently defined on the doctors table
SHOW INDEX FROM doctors;

-- Remove an index that is no longer needed
DROP INDEX idx_doctors_salary ON doctors;

-- Confirm it was removed
SHOW INDEX FROM doctors;

The first SHOW INDEX FROM doctors call lists every index on the table, including the automatic primary key index plus the two just created — idx_doctors_dept_name would appear as two rows, one for each column, with Seq_in_index values of 1 and 2 respectively, confirming department_id comes first. After DROP INDEX removes idx_doctors_salary, the second SHOW INDEX call confirms it no longer appears, while the doctors table's actual data remains completely untouched.

Real-World Examples

Database performance audits routinely start with SHOW INDEX across key tables to build a complete inventory before identifying redundant or missing indexes.
Schema cleanup projects use DROP INDEX to remove indexes tied to deprecated features or query patterns that are no longer part of an application's active codebase.
Database migration tools programmatically query index metadata (equivalent to SHOW INDEX) to generate documentation or comparison reports between environments.

Common Mistakes to Avoid

Never reviewing existing indexes with SHOW INDEX, allowing unnecessary or redundant indexes to accumulate silently over time.
Assuming DROP INDEX deletes data, when it only removes the performance-optimization structure.
Dropping an index without first confirming it's genuinely unused by any active query patterns.

Interview Questions

Q1. How do you view all indexes currently defined on a table?

Run SHOW INDEX FROM table_name, which returns detailed information about every index on that table, including index names, covered columns, uniqueness, and column order within composite indexes.

Q2. Does dropping an index affect the underlying table data?

No, DROP INDEX only removes the separate index structure used for performance optimization, leaving all of the table's actual stored data completely unaffected.

Q3. Why is it good practice to periodically review and remove unused indexes?

Every index, even an unused one, continues to add write overhead (since it must be maintained on every relevant INSERT, UPDATE, or DELETE) and consumes storage space, so removing genuinely unused indexes improves overall write performance without sacrificing any read performance that mattered.

Practice MCQs

1. What command displays all indexes on the doctors table?

DESCRIBE INDEX doctors;
SHOW INDEX FROM doctors;
LIST INDEX doctors;
SELECT * FROM INDEXES WHERE table='doctors';

Answer: B. SHOW INDEX FROM doctors;

Explanation: SHOW INDEX FROM table_name is the standard MySQL command for viewing detailed information about all indexes defined on a specific table.

2. What happens to a table's data when an index on it is dropped?

The data is also deleted
The data is completely unaffected
Only indexed rows are deleted
The table becomes read-only

Answer: B. The data is completely unaffected

Explanation: DROP INDEX removes only the separate index structure, having zero impact on the actual data stored in the table.

Quick Revision Points

SHOW INDEX FROM table_name lists all indexes and their details for a given table.
DROP INDEX index_name ON table_name removes an index without affecting the underlying data.
Regular index review and cleanup is a recommended, often-overlooked database maintenance practice.

Conclusion

SHOW INDEX reveals all indexes currently defined on a table, including their columns and uniqueness.
DROP INDEX safely removes an index without any impact on the table's actual data.
Periodically auditing and removing unused indexes improves write performance without sacrificing needed read performance.

SHOW INDEX FROM table_name provides a complete inventory of every index defined on a table, including each index's name, covered columns, uniqueness, and column ordering within composite indexes — an essential tool for auditing a schema's indexing strategy. DROP INDEX index_name ON table_name removes an index cleanly, without any impact on the underlying table's actual data, since an index is purely a separate performance-optimization structure. Together, these commands support the ongoing maintenance practice of reviewing and removing genuinely unused indexes, which otherwise continue to silently consume write performance and storage space indefinitely.