MySQL DROP Index

Introduction

In this chapter, we will learn about dropping indexes in MySQL. Indexes are essential for improving the speed of data retrieval operations, but they also consume storage space and can add overhead to data modification operations. Therefore, it might be necessary to drop an index if it is no longer needed or if it is negatively impacting performance. We will cover the syntax for dropping indexes, examples, and important considerations for dropping indexes in MySQL.

Syntax

The basic syntax for dropping an index in MySQL is:

DROP INDEX index_name ON table_name;

• index_name: The name of the index to be dropped.
• table_name: The name of the table from which the index will be dropped.

Example

Let’s go through an example where we create and then drop indexes on a table.

Full Example

1. Create a Database:

CREATE DATABASE company;

2. Select the Database:

USE company;

3. Create the Employees Table:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    employee_code VARCHAR(10) UNIQUE,
    department_id INT
);

4. Insert Data into the Employees Table:

INSERT INTO employees (first_name, last_name, email, employee_code, department_id) VALUES
('Rahul', 'Sharma', 'rahul.sharma@example.com', 'E001', 1),
('Priya', 'Singh', 'priya.singh@example.com', 'E002', 2),
('Amit', 'Kumar', 'amit.kumar@example.com', 'E003', 1),
('Neha', 'Verma', 'neha.verma@example.com', 'E004', 3),
('Sahil', 'Mehta', 'sahil.mehta@example.com', 'E005', 2);

5. Create a Regular Index on the last_name Column:

CREATE INDEX idx_last_name ON employees (last_name);

6. Query the Employees Table Using the Index:

Let’s perform a query to see how the index on the last_name column improves performance.

SELECT * FROM employees WHERE last_name = 'Singh';

Output

7. Drop the Index on the last_name Column:

DROP INDEX idx_last_name ON employees;

8. Query the Employees Table Again:

Let’s perform the same query again to verify that the index has been dropped.

SELECT * FROM employees WHERE last_name = 'Singh';

Output

In this example, the index idx_last_name is created to improve query performance, and then it is dropped when no longer needed.

Dropping Indexes on Other Tables

Let’s assume we have a table projects and we want to drop an index on it.

1. Create the Projects Table:

CREATE TABLE projects (
    project_id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    project_code VARCHAR(10),
    department_id INT
);

2. Add an Index to the Projects Table:

CREATE INDEX idx_project_name ON projects (project_name);

3. Insert Data into the Projects Table:

INSERT INTO projects (project_name, project_code, department_id) VALUES
('Project A', 'P001', 1),
('Project B', 'P002', 2),
('Project C', 'P003', 1),
('Project D', 'P004', 3);

4. Drop the Index on the project_name Column:

DROP INDEX idx_project_name ON projects;

5. Query the Projects Table:

Let’s perform a query to verify the data.

SELECT * FROM projects WHERE project_name = 'Project B';

Output

In this example, the index idx_project_name is created and then dropped, demonstrating how to manage indexes in MySQL.

Important Considerations

• Performance Impact: Dropping an index can impact the performance of queries that rely on that index. Ensure that dropping an index will not negatively affect critical queries.
• Storage Space: Dropping indexes can free up storage space, which can be beneficial if the index is no longer needed.
• Data Modification Overhead: Removing unnecessary indexes can reduce the overhead on data modification operations (INSERT, UPDATE, DELETE), leading to improved performance.

Conclusion

Dropping indexes in MySQL is an essential task for database maintenance and optimization. This chapter covered the syntax for dropping indexes, provided full examples, and discussed important considerations.