MySQL Drop Index

Sometimes we have an index that is not required in data operations. In that case, we can use this statement to remove an existing index from the table. We can delete single or multiple index columns from the table.

Syntax

The syntax of the delete index from the existing table is shown below.

ALTER TABLE table_name DROP INDEX index_name;

The syntax of the delete index from the table is shown below.

DROP INDEX index_name ON table_name [algorithm | lock];

The syntax of the delete index from the table with algorithm and lock types is shown below.

DROP INDEX index_name ON table_name [algorithm = {DEFAULT or INPLACE or COPY} |
Lock = {DEFAULT or NONE or SHARED or EXCLUSIVE} ];

The syntax of the delete primary index from the table is shown below.

DROP INDEX PRIMARY ON table name;

Algorithm

algorithm = { DEFAULT or INPLACE or COPY }

• The algorithm assigns a particular algorithm to remove the table index.
• The algorithm uses DEFAULT, INPLACE, and COPY clauses.
• The COPY algorithm clause helps to copy a new table using each row.
• The INPLACE clause allows rebuilding the table. This algorithm allows manipulating table statements.

Lock

Lock = {DEFAULT or NONE or SHARED or EXCLUSIVE}

• The lock controls the level of reads and writes the table while the index is deleting from the table.
• The lock has a DEFAULT, NONE, SHARED, and EXCLUSIVE option to remove the index column.
• The default sets a high level of read and write index simultaneously.
• The NONE lock allows read and write while deleting the index column. If the "none" lock does not allow, then the MySQL system shows an error.
• The SHARED lock allows read concurrently. If you do not allow read coexistence, then the MySQL system shows an error.
• The EXCLUSIVE lock access exclusive concurrent in the drop index query.

Examples of the delete index

1) Example: The basic delete index example shows below.

Execute the below query to delete unwanted indexes from the table. The index name and table name requires to delete the index.

mysql> drop index mark on index_table;

OUTPUT

Execute the below query to see the available index from the table.

mysql> show index from index_table;

In the above image, we can see that one row is removed from the table. The table shows the key name, column name, index type, visibility of the table.

2) Example: The basic delete index with database example shows below.

Execute the following query to delete the index from the available table. Here you will use the "alter" keyword with the "drop index" statement.

mysql> alter table index_table DROP index mark;

OUTPUT

Execute the below query to see the available index from the table.

mysql> show index from index_table;

In the above image, we can see that one row is removed from the table. The table shows the key name, column name, index type, visibility of the table.

3) Example: The basic delete index example with algorithm shows below.

Execute the following query to remove the index with an algorithm. Here, the query applies the INPLACE algorithm.

mysql> drop index mark on index_table algorithm = INPLACE;

OUTPUT

Execute the below query to see the available index from the table.

mysql> show index from index_table;

In the above image, we can see that one row is removed from the table. The table shows the key name, column name, index type, visibility of the table. The key name is an index name.

4) Example: The basic delete index with lock example shows below.

Execute the following query to remove the index with lock. Here, the query applies DEFAULT lock.

mysql> drop index mark on index_table LOCK = DEFAULT;

OUTPUT

Execute the below query to see the available index from the table.

mysql> show index from index_table;

In the above image, we can see that one mark is removed from the table. The table shows the key name (index name), column name, index type, unique key, and table visibility.

5) Example: The basic delete index with algorithm and lock example shows below.

Execute the following query to remove the index with algorithm and lock. Here, the query applies the DEFAULT algorithm and lock.

mysql> drop index mark on index_table algorithm = DEFAULT LOCK = DEFAULT;

OUTPUT

Execute the below query to see the available index from the table.

mysql> show index from index_table;

In the above image, we can see that the mark row is removed from the table. The table shows the critical name, column name, index type, visibility of the table.

6) Example: The delete index with different algorithms and lock examples show below.

Execute the following query to remove the primary index with algorithm and lock. Here, the query applies copy algorithm and none lock.

mysql> drop index mark on index_table algorithm = COPY LOCK = SHARED;

OUTPUT

Execute the below query to see the available index from the table.

mysql> show index from index_table;

The above output image removes the mark row from the table. The table shows the key name, column name, index type, visibility of the table. Here copy algorithm requires a lock; otherwise, the MySQL system shows an error.