MySQL lets you create indexes. When you need to free up a space or change your database schema, you may start by dropping the indexes in your table. We will give the examples on how to create an index on MySQL. Furthermore, we will discuss how to drop the indexes from the MySQL table. Let’s begin!
Why Use the Indexes?
After you create a MySQL database, the next thing is creating the table to hold the data. As the database administrator, you must find ways to improve the speed of your database operations, especially the SELECT command where numerous rows are involved.
When MySQL encounters a search query, it searches row by row to find the relevant row that contains the particular data. However, you can improve the database operation through indexes. The indexes act as data structures that help to find the rows using the column values instead of going through every row in your table.
Indexes aim to improve the operation speed. You can create them with the following syntax:
Let’s quickly create a table and index for this tutorial. We name our table as “indexdemo”.
Next, let’s create our index for the name column. We name it as “nameindex”.
Confirm the created indexes with the following syntax:
The output confirms that our index is created successfully. Read the post to learn how to drop the created index from your MySQL table.
How to Drop the Indexes from the MySQL Table
When you have indexes in your MySQL table, you can drop them to free up a space or create a room to organize your schema. You can drop the indexes using the default index algorithm and lock option or select which ones to use.
The default syntax to use is as follows:
Here, the command uses the default lock type based on the default algorithm which is the COPY algorithm.
Alternatively, you can specify the algorithm and the lock with the following syntax:
For the algorithm, you can use the INPLACE option that drops the index without creating any temporary table to enhance the speed of dropping the index. Avoid this option if you are working with large data and go for the default one.
As for the lock, you can use three options aside from the default option. The NONE lock type is ideal for read-only operations. No lock is acquired when dropping the table. Specify it as follows and optionally combine it with an algorithm.
The second type is the SHARED lock which specifies the table to have a shared lock when dropping the index. That way, the other sessions will access the table where the drop command occurs but can’t modify it. Here’s how you specify it:
Lastly, the EXCLUSIVE lock type prevents other sessions from utilizing or accessing the table until the index is dropped successfully. Its syntax is as follows:
Let’s use the default algorithm and the lock option by specifying the following command:
If we check the available indexes, we can confirm that our previous index is successfully dropped from our table.
We get an empty set as our output.
Conclusion
MySQL and other DBMS use indexes to speed up the operations in the database. When you add the indexes, searching becomes easier which saves time and resources. This post detailed the process on how to drop the indexes from the MySQL table. We learned the steps to create an index and show the available indexes, and concluded it with dropping the index. Moreover, we mentioned the different options to use when dropping the indexes.
