MariaDB Drop Index

Summary: in this tutorial, you will learn how to use the MariaDB drop index statement to remove an index from a table.

Introduction to the MariaDB drop index statement

The drop index statement allows you to remove an index from a table.

Here is the syntax of the drop index statement:

drop index [if exists] index_name 
on table_name
Code language: SQL (Structured Query Language) (sql)

[algorithm_option | lock_option]

In this syntax:

• First, specify the name of the index that you want to delete after the drop index keywords. The optional if exists clause conditionally removes the index only if it exists.
• Second, specify the name of the table to which the index associates after the on keyword.
• Third, specify the algorithm or lock option used for index removal operation.

Note that the if exists clause is available in MariaDB 10.1.14 or later.

MariaDB drop index statement example

First, create a new table called trees:

create table trees(
    id int auto_increment,
    name varchar(120) not null,
    primary key(id)
);
Code language: SQL (Structured Query Language) (sql)

Next, create an index on the name column of the trees table:

create index tree_names
on trees(name);
Code language: SQL (Structured Query Language) (sql)

Then, show the indexes of the trees table:

show indexes from trees;
Code language: SQL (Structured Query Language) (sql)

After that, drop the tree_names index:

drop index tree_names
on trees;
Code language: SQL (Structured Query Language) (sql)

Finally, show the indexes of the trees table again to verify the removal:

show indexes from trees;
Code language: SQL (Structured Query Language) (sql)

Removing the primary key index

If the primary key of a table is an auto_increment column, you must remove the auto_increment property from the primary key column before removing the primary index.

The following example illustrates how to drop the primary index of the trees table.

First, remove the auto_increment property from the id column:

alter table trees
modify id int;
Code language: SQL (Structured Query Language) (sql)

Second, drop the primary index. Note that the primary is surrounded by backtick characters (`):

drop index `primary`
on trees;
Code language: SQL (Structured Query Language) (sql)

Finally, show the indexes of the trees table:

show indexes from trees;
Code language: SQL (Structured Query Language) (sql)

It returns an empty result set.

In this tutorial, you learned how to use the MariaDB drop index statement to remove an index from a table.