CREATE and DROP INDEX Statement in SQL

In SQL, indexes are utilized to enhance the speed of database searches by allowing the database engine to quickly locate and retrieve specific data. The commands CREATE INDEX and DROP INDEX are employed in SQL databases to create and remove indexes.

CREATE INDEX

To establish an index on one or more table columns, use the CREATE INDEX statement. The following are the fundamental syntax for constructing an index −

CREATE INDEX index_name
ON table_name (column1, column2, ...);

In the following SQL command, the index that has to be created is referred to as index_name, the table on which the index will be added is referenced as table_name, and the columns on which the index will be based are referenced as column1, column2, and so on.

Suppose we have a database called "employees" that includes the following columns "id," "name," "salary," and "department."

Input Table

+----+--------+--------+-------------+
| id |  name  | salary |  department |
+----+--------+--------+-------------+
| 1  | Alice  | 50000  | Engineering |
| 2  | Bob    | 60000  | Marketing   |
| 3  | Charlie| 75000  | Sales       |
| 4  | Dave   | 65000  | Engineering |
| 5  | Eve    | 55000  | Marketing   |
+----+--------+--------+-------------+

To create an index on any column, let’s say the salary column, the SQL command used will be

CREATE INDEX salary_index
ON employees (salary);

On the salary column of the worker's table, a new index with the name salary_index will be created.

Output Table

There would be no discernible change in the table structure itself, though, if you were inquiring about the "employees" table's final structure following the creation of the index. The sole modification is that queries using the "salary" column will now be able to take advantage of the new "salary_index" index, which will be built in the background. The columns and rows of the table would remain the same.

DROP INDEX

An index may be removed from a table using the DROP INDEX command. The following are the fundamental syntax for dropping an index −

DROP INDEX index_name
ON table_name;

The index that will be removed is denoted by index_name, and the table from which the index will be deleted is denoted by table_name.

For example, we might use the SQL statement below to remove the previously constructed salary_index index −

Input Table

+----+--------+--------+-------------+
| id |  name  | salary |  department |
+----+--------+--------+-------------+
| 1  | Alice  | 50000  | Engineering |
| 2  | Bob    | 60000  | Marketing   |
| 3  | Charlie| 75000  | Sales       |
| 4  | Dave   | 65000  | Engineering |
| 5  | Eve    | 55000  | Marketing   |
+----+--------+--------+-------------+

DROP INDEX salary_index
ON employees;

By doing this, the salary_index index will be deleted from the worker's table.

Output Table

There would be no discernible change in the table structure itself, though, if you were inquiring about the "employees" table's final structure following the deletion of the index. The sole modification is that queries using the "salary" column will now not be able to take advantage of the new "salary_index" index, which was previously built in the background. The columns and rows of the table would remain the same.

It's crucial to remember that while indexes can significantly affect database speed, they can also make insert, update, and delete processes slower. Given your application's specific requirements, it is critical to carefully consider which columns to index and when to construct or remove indexes.

Conclusion

The CREATE INDEX and DROP INDEX commands are utilized in a SQL database to add or remove indexes. To establish an index, it is necessary to specify the table where it will be created and the columns it will be based on in the syntax. Conversely, when removing an index, it is essential to indicate both the index name and the table from which it will be deleted.