MySQL DROP INDEX

Summary: in this tutorial, you will learn how to use the MySQL DROP INDEX statement to remove existing indexes of a table.

Introduction to MySQL DROP INDEX statement

To delete an existing index from a table, you use the DROP INDEX statement as follows:

DROP INDEX index_name 
ON table_name
[algorithm_option | lock_option];Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the index which you want to remove after the DROP INDEX keywords.
• Second, specify the name of the table to which the index belongs.

Algorithm

The algorithm_option allows you to specify a specific algorithm used for the index removal.

The following shows the syntax of the algorithm_option clause:

ALGORITHM [=] {DEFAULT|INPLACE|COPY}Code language: SQL (Structured Query Language) (sql)

For the index removal, the following algorithms are supported:

• COPY: MySQL copies data from the original table to a new table row by row. After completing the copy, the DROP INDEX executes on the newly created table. Please note that during the copy process, you’re not allowed to perform any concurrent data manipulation statements such as INSERT and UPDATE.
• INPLACE: The INPLACE algorithm involves rebuilding the table directly in its existing location without creating a new copy of the table. During both the preparation and execution phases of the index removal process, MySQL places an exclusive metadata lock on the table. This allows concurrent data manipulation statements to be executed alongside the index removal process.

Note that the ALGORITHM clause is optional. If you skip it, MySQL uses INPLACE. In case the INPLACE is not supported, MySQL uses COPY.

Using DEFAULT has the same effect as omitting the ALGORITHM clause.

Lock

The lock_option controls the level of concurrent reads and writes on the table while the index is being removed.

The following shows the syntax of the lock_option:

LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}Code language: SQL (Structured Query Language) (sql)

The following locking modes are supported:

•  DEFAULT: This allows you to have the maximum level of concurrency for a given algorithm. First, it allows concurrent reads and writes if supported. If not, allow concurrent reads if supported. If not, enforce exclusive access.
•  NONE: If the NONE is supported, you can have concurrent reads and writes. Otherwise, MySQL issues an error.
•  SHARED: If the SHARED is supported, you can have concurrent reads but not writes. MySQL issues an error if the concurrent reads are not supported.
•  EXCLUSIVE: This enforces exclusive access.

MySQL DROP INDEX statement examples

Let’s create a new table for the demonstration:

CREATE TABLE leads(
    lead_id INT AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    information_source VARCHAR(255),
    INDEX name(first_name,last_name),
    UNIQUE email(email),
    PRIMARY KEY(lead_id)
);Code language: SQL (Structured Query Language) (sql)

The following statement removes the name index from the leads table:

DROP INDEX name ON leads;Code language: SQL (Structured Query Language) (sql)

The following statement drops the email index from the leads table with a specific algorithm and lock:

DROP INDEX email ON leads
ALGORITHM = INPLACE 
LOCK = DEFAULT;Code language: SQL (Structured Query Language) (sql)

MySQL DROP PRIMARY KEY index

To drop the primary key whose index name is PRIMARY, you use the following statement:

DROP INDEX `PRIMARY` ON table_name;Code language: SQL (Structured Query Language) (sql)

Notice that the PRIMARY is a reversed word in MySQL. However, the index name is PRIMARY. Therefore, you need to place the PRIMARY inside the quotes to specify the primary index.

The following statement creates a new table named t with a primary key:

CREATE TABLE t(
    pk INT PRIMARY KEY,
    c VARCHAR(10)
);Code language: SQL (Structured Query Language) (sql)

To drop the primary key index, you use the following statement:

DROP INDEX `PRIMARY` ON t;Code language: SQL (Structured Query Language) (sql)

Summary

• Use the DROP INDEX statement to remove an existing index.
• Use the DROP INDEX `PRIMARY` to remove the primary index.