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.

MySQL DROP INDEX statement syntax

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

DROP INDEX index_name ON table_name
[algorithm_option | lock_option];

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}

For the index removal, the following algorithms are supported:

• COPY: The table is copied to the new table row by row, the DROP INDEX is then performed on the copy of the original table. The concurrent data manipulation statements such as INSERT and UPDATE are not permitted.
• INPLACE: The table is rebuilt in place instead of copied to the new one. MySQL issues an exclusive metadata lock on the table during the preparation and execution phases of the index removal operation. This algorithm allows for concurrent data manipulation statements.

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}

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)
);

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

DROP INDEX name ON leads;

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;

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;

The following statement creates a new table named twith a primary key:

CREATE TABLE t(
    pk INT PRIMARY KEY,
    c VARCHAR(10)
);

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

DROP INDEX `PRIMARY` ON t;

In this tutorial, you have learned how to use the MySQL DROP INDEX statement to remove an existing index of a table.