Dropping an Index in MariaDB

In MariaDB, indexes play a crucial role in enhancing query performance by facilitating fast data access within a table. However, there are scenarios where you might need to drop indexes. This article explores various methods to drop indexes in MariaDB, including using DROP INDEX, ALTER TABLE DROP INDEX, and ALTER TABLE DROP KEY statements. Additionally, it covers dropping a primary key index using ALTER TABLE DROP PRIMARY KEY.

What is an Index in MariaDB?

An index in MariaDB is a type of database structure that facilitates fast and easy access to data within a table. It functions similarly to a book’s index in that it gives you a pointer to the location of particular data, making it possible for you to find information more quickly. Let’s see how we drop an index using the below methods as follows.

1. Using DROP INDEX Statement
2. Using ALTER TABLE Statement with DROP INDEX Clause
3. Using ALTER TABLE Statement with DROP KEY Clause

Let’s set up an environment for DROP an Index

To understand How to Drop an index in MariaDB we need a table on which we will perform various operations. So we create a table example_table.

Creating the table example_table with PRIMARY KEY

Query:

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    email VARCHAR(100),
    is_student BOOLEAN
);

Creating an Index on example_table (name, age, email) column.

Query:

CREATE INDEX name_index ON example_table (name);
CREATE INDEX age_index ON example_table (age);
CREATE INDEX email_index ON example_table (email);

Showing the Indexes on table example_table

Query:

SHOW INDEX FROM example_table;

Output:

Output

1. Using DROP INDEX Statement

In MariaDB, to remove an existing index from a table, use the DROP INDEX statement. Structures called indexes let database tables retrieve data more quickly. When an index is no longer needed or you wish to replace it with a different kind of index, dropping it can be helpful.

Syntax:

DROP INDEX idx_name ON table_name;

Replace table_name with the name of the table where the index is declared and index_name with the name of the index you wish to drop.

Example:

DROP INDEX name_index ON example_table;

-- Showing the Indexes defined on example_table
SHOW INDEX FROM example_table;

Output:

Ouput for DROP INDEX statement

Explanation: The above query remove the name_index Index defined on example_table.

2. Using ALTER TABLE Statement with DROP INDEX Clause

In MariaDB, the ALTER TABLE statement is used to change an existing table’s structure. It lets you adjust restrictions, indexes, and more. It also lets you add, remove, or alter columns.

Syntax:

ALTER TABLE table_name DROP INDEX index_name;

Replace table_name with the name of the table where the index is declared and index_name with the name of the index you wish to drop.

Example:

ALTER TABLE example_table DROP INDEX age_index;

-- Showing the Indexes defined on example_table
SHOW INDEX FROM example_table;

Output:

Output of ALTER TABLE Statement with DROP INDEX Clause

Explanation: The above query remove the age_index Index defined on example_table.

3. Using ALTER TABLE Statement with DROP KEY Clause

In MariaDB, you can change an existing table’s structure with the ALTER TABLE statement. An existing index in a table can be selectively removed using the ALTER TABLE statement’s DROP KEY clause.

Syntax:

ALTER TABLE table_name DROP KEY index_name;

Replace table_name with the name of the table where the index is declared and index_name with the name of the index you wish to drop.

Example:

ALTER TABLE example_table DROP KEY email_index;

-- Showing the Indexes defined on example_table
SHOW INDEX FROM example_table;

Output:

Output of ALTER TABLE Statement with DROP KEY Clause

Explanation: The above query remove the email_index Index defined on example_table.

Dropping the PRIMARY KEY Index

In MariaDB, A column, or group of columns, in a database that uniquely identifies every row in the table is called a primary key. The values in the designated columns are guaranteed to be unique and non-NULL by the primary key requirement. A primary key column, or group of columns, is also utilised to guarantee entity integrity and offer a rapid and effective means of searching through information.

Using the ALTER TABLE Statement to Remove a Primary Key

To drop a primary key index in MariaDB, we can use the ALTER TABLE statement with the DROP PRIMARY KEY clause.

Syntax:

ALTER TABLE table_name
DROP PRIMARY KEY;

Replace table_name with name of the table.

Before Dropping the Primary Key, Our table looks:

Before Remove the primary key

Now we will DROP Primary Key:

ALTER TABLE example_table
DROP PRIMARY KEY;

After Dropping the PRIMARY KEY:

AFTER Dropping the PRIMARY KEY

Explanation: Now the primary key has been removed.

Conclusion

In conclusion, MariaDB, an open-source RDBMS, provides efficient ways to manage indexes, crucial for enhancing query performance. This article discussed three methods for dropping indexes using `DROP INDEX`, `ALTER TABLE DROP INDEX`, and `ALTER TABLE DROP KEY`. Additionally, the process of dropping a primary key index using `ALTER TABLE DROP PRIMARY KEY` was covered. Effective index management is vital for maintaining a well-optimized database structure.