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 statement as follows:

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:

Code language: SQL (Structured Query Language) (sql)

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:

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:

Code language: SQL (Structured Query Language) (sql)

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

Code language: SQL (Structured Query Language) (sql)

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

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:

Code language: SQL (Structured Query Language) (sql)

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

Code language: SQL (Structured Query Language) (sql)

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

Code language: SQL (Structured Query Language) (sql)

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