Index Statement In MySQL

Dropping Index in MySQL

A DROP statement is used to drop a primary and non-primary key index. The syntax for dropping primary and non-primary keys is different in MySQL.

There are two commands to drop the index in MySQL. Let’s discuss both of them.

DROP INDEX Command

It is used to remove an existing index from a table. By dropping the index, it eliminates the data associated with it and increases the performance of operations on the table.

Syntax

The basic syntax to drop the non-primary key is:-

DROP INDEX index_name ON table_name;

The primary keywords are reserved words, so backticks are required to drop the primary key index. The syntax to drop the primary key is:-

DROP INDEX `PRIMARY_Name` ON table_name;

 

ALTER TABLE Command

The ALTER TABLE Command directly cannot drop an index in MySQL. So, to remove an index, DROP INDEX is used along with ALTER TABLE to remove the existing index.

Syntax

The basic syntax to drop non-primary key using ALTER TABLE is:-

ALTER TABLE table_name DROP INDEX index_name; 

The syntax to drop the primary key is:-

ALTER TABLE table_name DROP PRIMARY KEY;

In the above syntax, backticks are not required since, this time, it is a reserved word.

Note: It is necessary to specify the table name since MySQL permits to index name to be reused on multiple tables.

SHOW INDEX Command

SHOW indexes command  will provide details about the indexes defined on the "temp" table, such as the index name, the index's included columns' names, the index's type, and other information.

Syntax

The basic syntax to show indexes from Table is:-

SHOW indexes FROM table_name;

Example to Understand Index Statement

Let’s understand creating and dropping index statements with the help of examples.

1. First, Construct a table called the employee to contain employee data. Employee_id, first_name, last_name, email, and salary will be columns in the table. To enhance search functionality, we will establish an index on the email column.

The MySQL query to create a table is:-

CREATE TABLE employee (
	employee_id INT PRIMARY KEY,
	first_name VARCHAR(50),
	last_name VARCHAR(50),
	email VARCHAR(100),
	salary DECIMAL(10, 2)
);

Output:

2. Now, insert the data inside the table to perform an indexing operation. 

INSERT INTO employee (employee_id, first_name, last_name, email, salary)
VALUES
(2, 'Jane', 'Smith', 'jane.smith@example.com', 6000.00),
(3, 'David', 'Johnson', 'david.johnson@example.com', 7000.00),
(4, 'Sarah', 'Williams', 'sarah.williams@example.com', 5500.00);

 

Output:

3. Now, we will create an index on the email column to optimize the performance of queries. 

CREATE INDEX idx_e ON employee (email);

The above command will set the email column as an index in the background.

4. To show the indexes from table employee in MySQL Databases, write the below query.

SHOW indexes FROM employee;

Output:

5. To find the employee with specific mail, the user can write the below query that will enhance the performance of the table.

SELECT *
FROM employee
WHERE email = 'jane.smith@example.com';

Output:

6. Index can be dropped by using DROP Command.

ALTER TABLE employee
DROP INDEX idx_e;

Advantages of using Indexes in MySQL

The advantage of using indexes in MySQL are:-

• Indexing allows us to search the data more efficiently and fastly. It is used to improve the query performance.
   
• Indexing improves the performance of the join operation.
   
• It is used to speed up the sorting operation.
   
• It is used to apply unique and primary key constraints.
   
• It provides flexibility and allows users to create more complex and efficient queries.

Disadvantages of using Indexes in MySQL

The disadvantages of using indexes in MySQL are:-

• It requires extra storage space depending on the number of rows and area of columns.
   
• Since data changes over time, indexes need to be maintained.
   
• Fragmentation can decrease Indexing performance and increase disk I/O Operations.
   
• Indexing slows down the data modification operation due to the updation of the index each time.

Frequently Asked Questions

What does the MySQL CREATE INDEX statement serve?

The speed of database activities like searching, sorting, and filtering data is improved by an index. It makes queries more effective by enabling the database to find matching rows rapidly.

How can I employ MySQL's CREATE INDEX statement?

The table name, the desired index column(s), and the index name must all be specified to build an index. CREATE INDEX index_name ON table_name (column_name) is the syntax.

What distinguishes a non-unique index from a unique index?

A unique index assures that the indexed column(s) have unique values, allowing no duplicate entries. A non-unique index, on the other hand, permits duplicate values in the indexed column(s).

How can an index be deleted once it has been created?

The DROP INDEX command can be used to delete an index. To delete an index, specify its name and the table it belongs to before running the query.

Conclusion

Index Statement in MySQL is a valuable tool for improving the efficiency of database queries by building indexes on one or more table fields. In this article, we have discussed the creation and dropping of the index in the table.

Do not stop learning! We recommend you read some of our related articles: 

1. MySQL
2. MySQL Features
3. Indexes in MySQL
    

Refer to our Guided Path to upskill yourself in DSA, Competitive Programming, JavaScript, System Design, and many more! If you want to test your competency in coding, you may check out the mock test series and participate in the contests hosted on Coding Ninjas Studio!

But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. For placement preparations, you must look at the problems, interview experiences, and interview bundles.

We wish you Good Luck! 

Happy Learning!