Delete or DROP a column from the INDEX in MySQL

MySQL – Delete or Drop a Column from an Index

In one of previous MySQL Tutorials, we have learned to add a column to the INDEX. In this tutorial, we shall learn how to remove an index and how to remove one column from a composite index in MySQL.

The distinction is important: DROP INDEX removes the complete named index. It does not delete the indexed column or its data. To remove only one column from a multi-column index while keeping the other indexed columns, drop the existing index and create it again with the required column list.

What MySQL DROP INDEX removes

• DROP INDEX removes an entire secondary index definition.
• The table columns and stored row data remain unchanged.
• Dropping a UNIQUE index also removes the uniqueness rule enforced by that index.
• Removing one key part from a composite index requires dropping and recreating the index.
• Deleting a column from the table requires ALTER TABLE ... DROP COLUMN, which is a different operation.

MySQL syntax to drop a named index

To drop a complete named secondary index with ALTER TABLE, use the following syntax:

ALTER TABLE table_name DROP INDEX index_name;

This statement drops the complete index identified by index_name. MySQL also supports the equivalent standalone DROP INDEX statement.

DROP INDEX index_name ON table_name;

Check the MySQL index name and columns before dropping it

Use SHOW INDEX before changing an index. The result identifies the index name in Key_name, the indexed column in Column_name, and the column order in Seq_in_index.

SHOW INDEX FROM students;

For a complete table definition, including primary keys, unique indexes, prefix lengths, and index column order, inspect SHOW CREATE TABLE as well.

SHOW CREATE TABLE students;

Example: drop sectionIndex from the students table

We can add one or more columns as a single entry to the INDEX using an index name.

For example, the INDEX of students table is

Values in boxes are the index names and those underlined are column names in the respective index.

Now we shall delete the index, sectionIndex, from the INDEX of students table.

Following is the query to delete sectionIndex from the INDEX:

ALTER TABLE students DROP INDEX sectionIndex;

The query removes the complete index named sectionIndex. It does not remove the indexed column from the students table.

To confirm if the INDEX has been modified, check the INDEX of students table.

Remove one column from a composite index in MySQL

MySQL does not provide syntax that edits the column list of an existing index in place. Suppose studentLookupIndex currently indexes student_name, section, and roll_number.

CREATE INDEX studentLookupIndex
ON students (student_name, section, roll_number);

To remove section from this composite index but keep the other two indexed columns, drop and recreate the index. Both clauses can be included in one ALTER TABLE statement.

ALTER TABLE students
    DROP INDEX studentLookupIndex,
    ADD INDEX studentLookupIndex (student_name, roll_number);

Preserve all required index properties when recreating it. If the original index was unique, use ADD UNIQUE INDEX. Also preserve column order, prefix lengths, sort direction, visibility, and any other options shown by SHOW CREATE TABLE.

ALTER TABLE students
    DROP INDEX uniqueStudentIndex,
    ADD UNIQUE INDEX uniqueStudentIndex (student_name, roll_number);

Column order matters in a composite index. An index on (student_name, roll_number) is not equivalent to an index on (roll_number, student_name). Review the queries that use the index before choosing the new order.

DROP INDEX compared with DROP COLUMN in MySQL

Operation	What it removes	Does table data change?
ALTER TABLE ... DROP INDEX	The complete named index	No table column or row value is deleted
ALTER TABLE ... DROP COLUMN	The column from the table	Yes, all values stored in that column are removed
Drop and recreate a composite index	One or more key parts from the index definition	No table column or row value is deleted

Use DROP COLUMN only when the column itself must be deleted from the table.

ALTER TABLE students DROP COLUMN section;

When a table column is dropped, MySQL also removes that column from indexes that contain it. This is not the correct approach when the column must remain available to queries and only its index membership should change.

Drop a primary key instead of a secondary index

A primary key is handled separately from a normal named secondary index. Use DROP PRIMARY KEY with ALTER TABLE.

ALTER TABLE students DROP PRIMARY KEY;

Check foreign keys, application assumptions, replication requirements, and server settings before removing a primary key. InnoDB tables commonly depend on a suitable primary or unique key for row identification and efficient access.

Dependencies to review before dropping a MySQL index

1. Foreign keys: InnoDB can reject removal of an index required to support a foreign key constraint. Inspect SHOW CREATE TABLE first.
2. Unique constraints: Dropping a unique index permits future duplicate combinations unless another constraint prevents them.
3. Query performance: Queries that used the index may switch to another index or a table scan. Compare EXPLAIN plans before and after the change.
4. Composite-index order: Recreating an index with a different column order changes which leftmost prefixes MySQL can use.
5. DDL impact: Index changes can consume CPU, storage, and I/O and may affect concurrent work. Test the operation with the same MySQL version, storage engine, and representative table size.

Verify that the MySQL index was removed

Filter SHOW INDEX by the expected key name after the change. No returned row means the named index is no longer present.

SHOW INDEX FROM students
WHERE Key_name = 'sectionIndex';

You can also check the data dictionary through INFORMATION_SCHEMA.STATISTICS. This is useful in deployment scripts that need to test whether an index exists before deciding whether to run a separate DROP INDEX statement.

SELECT COUNT(*) AS index_exists
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'students'
  AND index_name = 'sectionIndex';

MySQL DROP INDEX FAQs

Can I remove only one column from a composite index in MySQL?

Not by altering the index column list directly. Drop the existing composite index and create it again with only the columns that should remain. Preserve UNIQUE, column order, prefix lengths, and other index options.

Does DROP INDEX delete the indexed column or its data?

No. DROP INDEX removes the index structure only. The table column and all values stored in it remain available.

What is the difference between DROP INDEX and DROP COLUMN?

DROP INDEX removes a lookup or constraint structure. DROP COLUMN removes the column definition and the data stored in that column.

How do I find an index name in MySQL?

Run SHOW INDEX FROM table_name. Read the Key_name value for the index name, Column_name for each indexed column, and Seq_in_index for the column order.

Can I use DROP INDEX IF EXISTS in MySQL?

The MySQL DROP INDEX syntax does not include an IF EXISTS clause. Check INFORMATION_SCHEMA.STATISTICS or SHOW INDEX first, and execute the drop conditionally in the application or migration process.

MySQL index removal verification checklist

• Confirm that the supplied name is an index name, not a column name.
• Record the current definition with SHOW CREATE TABLE before changing a composite or unique index.
• Verify whether the index supports a foreign key or uniqueness requirement.
• Preserve the intended column order when recreating a composite index.
• Run SHOW INDEX after the change and compare important query plans with EXPLAIN.

MySQL DROP INDEX reference

For complete syntax and version-specific behavior, refer to the official MySQL documentation for ALTER TABLE, DROP INDEX, and SHOW INDEX.