MySQL Drop Index
MySQL allows a DROP INDEX statement to remove the existing index from the table. To delete an index from a table, we can use the following query:
If we want to delete an index, it requires two things:
The Drop Index syntax contains two optional options, which are Algorithm and Lock for reading and writing the tables during the index modifications. Let us explain both in detail:
The algorithm_option enables us to specify the specific algorithm for removing the index in a table. The syntax of algorithm_option are as follows:
The Drop Index syntax supports mainly two algorithms which are INPLACE and COPY.
COPY: This algorithm allows us to copy one table into another new table row by row and then DROP Index statement performed on this new table. On this table, we cannot perform an INSERT and UPDATE statement for data manipulation.
INPLACE: This algorithm allows us to rebuild a table instead of copy the original table. We can perform all data manipulation operations on this table. On this table, MySQL issues an exclusive metadata lock during the index removal.
Note: If you not defined the algorithm clause, MySQL uses the INPLACE algorithm. If INPLACE is not supported, it uses the COPY algorithm. The DEFAULT algorithm works the same as without using any algorithm clause with the Drop index statement.
This clause enables us to control the level of concurrent reads and writes during the index removal. The syntax of lock_option are as follows:
In the syntax, we can see that the lock_option contains four modes that are DEFAULT, NONE, SHARED, and EXCLUSIVE. Now, we are going to discuss all the modes in detail:
SHARED: This mode supports only concurrent reads, not concurrent writes. When the concurrent reads are not supported, it gives an error.
DEFAULT: This mode can have the maximum level of concurrency for a specified algorithm. It will enable concurrent reads and writes if supported otherwise enforces exclusive mode.
NONE: You have concurrent read and write if this mode is supported. Otherwise, it gives an error.
EXCLUSIVE: This mode enforces exclusive access.
First, execute the following command to show the indexes available in the table.
It will give the following output.
In the output, we can see that there are three indexes available. Now, execute the following statement to removes the class index from table student.
Again, execute the SHOW INDEXES statement to verify the index is removed or not. After performing this statement, we will get the following output, where only two indexes are available.
Example using Algorithm and Lock
The following statement drops the age index form the student table using an algorithm and a lock option.
MySQL Drop PRIMARY Key Index
In some cases, the table contains a PRIMARY index that was created whenever you create a table with a primary key or unique key. In that case, we need to execute the following command because the PRIMARY is a reserved word.
To remove the primary key index from the student table, execute the following statement: