MySQL DROP INDEX

Sreeram Sreenivasan

Sometimes you may need to delete index in MySQL, if they are not performing as expected. Here’s how to drop index in MySQL using MySQL DROP INDEX query.

 

How to Drop Index in MySQL

Here are the steps to DROP INDEX in MySQL. We will use MySQL DROP INDEX query to remove existing index from a table.

Here’s the syntax of MySQL DROP INDEX command

DROP INDEX index_name ON table_name
[algorithm_option | lock_option];

In the above query, replace index_name with name of index, and table_name with your database table name.

Bonus Read : MySQL DROP FOREIGN KEY

You can also provide 2 optional arguments – algorithm_option and lock_option.

algorithm_option can have either of 3 values – default, inplace, copy. Depending on your argument, MySQL will use different algorithms to drop index.

Its syntax is

ALGORITHM [=] {DEFAULT|INPLACE|COPY}
  • default – This has the same effect as not using algorithm_option argument
  • copy – The table where you need to delete index, will be copied and the index will be deleted from the copy table. During this time, concurrent operations such as INSERT and UPDATE are not permitted.
  • inplace – In this case, the table is rebuilt in place without index. Although this option allows concurrent operations, it puts a metadata lock during execution.

Depending on lock_option MySQL will lock/not lock read/write access to your tables while index is being deleted. lock_option has the following syntax

LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

It takes 4 values:

  • default – it allows maximum concurrency for your choice of algorithm. it allows both concurrent reads and writes if supported. If not, it allows concurrent reads only. If that is also not supported, it enforces exclusive access.
  • none – if supported, then you can have concurrent reads and writes. Otherwise, MySQL gives an error.
  • shared – if shared option is supported, you can have concurrent reads, but not concurrent writes.
  • exclusive – this option enforces exclusive access

Bonus Read : MySQL DROP DATABASE

 

MySQL DROP INDEX example

Let’s say you have the following table with index.

mysql> create table orders(order_id int primary key,
     order_date date,
     product varchar(255),
     sale int,
     index name(product)
     );

 

Here’s the SQL query to drop existing index name from table orders.

mysql> DROP INDEX name ON orders;

Bonus Read : MySQL DROP COLUMN

 

MySQL DROP PRIMARY KEY Index

Here’s the syntax to drop primary key index on table orders.

mysql> DROP INDEX `PRIMARY` ON orders;

 

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Related posts:

How to Fill Missing Dates in MySQL?
MySQL DROP UNIQUE CONSTRAINT
How to Transpose Rows to Columns Dynamically in MySQL