RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-drop-index-statetement/ below:

MySQL DROP INDEX Statement - GeeksforGeeks

MySQL DROP INDEX Statement

Last Updated : 23 Jul, 2025

MySQL, an open-source relational database management system, is widely used for web-based applications. Managing indexes in MySQL is important for maintaining optimal database performance. Indexes improve query speed by allowing quick data retrieval, but sometimes you may need to remove an index to optimize performance or reclaim disk space. In this article, we'll explore how to use the DROP INDEX statement in MySQL.

What is the DROP INDEX Statement?

The DROP INDEX statement in MySQL is used to delete an existing index from a table. Removing unnecessary or redundant indexes can help optimize database performance and manage storage effectively.

Syntax of DROP INDEX:

The basic syntax for the DROP INDEX statement is as follows:

DROP INDEX index_name ON table_name;

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

Example of DROP INDEX Create a Table and Insert Data

First, we'll create a products table and insert some sample data.

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2)
);INSERT INTO products (product_name, category, price) VALUES

('Laptop', 'Electronics', 1200.00),
('Smartphone', 'Electronics', 800.00),
('Coffee Maker', 'Appliances', 100.00),
('Running Shoes', 'Clothing', 80.00),
('Digital Camera', 'Electronics', 500.00);

Create an Index

Next, we create an index on the category column.

CREATE INDEX idx_category ON products (category);

Explanation: This command creates an index named idx_category on the category column of the products table. This index helps speed up queries that filter by the category column.

Verify the Index

To verify the index creation, use the SHOW INDEX statement.

SHOW INDEX FROM products;

Expected Output:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression products 1 idx_category 1 category A 1 NULL NULL YES BTREE YES NULL products 0 PRIMARY 1 product_id A 5 NULL NULL BTREE YES NULL

Explanation: This output shows two indexes: the PRIMARY index on product_id and the idx_category index on the category column.

Drop the Index

Now, let's drop the idx_category index.

DROP INDEX idx_category ON products;

Explanation: This command removes the idx_category index from the products table.

Verify the Index Deletion

To confirm the index has been dropped, use the SHOW INDEX statement again.

SHOW INDEX FROM products;

Expected Output:

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression products 0 PRIMARY 1 product_id A 5 NULL NULL BTREE YES NULL

Explanation: The output now only shows the PRIMARY index on product_id, indicating that the idx_category index has been successfully dropped.

Conclusion

Dropping an index in MySQL is a simple but powerful operation that can impact database performance. In this example, we demonstrated how to create a table, add an index, verify its existence, drop the index, and then confirm its removal. Properly managing indexes is necessary for optimizing query performance and efficient database management.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4