CREATE and DROP INDEX Statement in SQL

The CREATE INDEX statement in SQL is a powerful tool used to enhance the efficiency of data retrieval operations by creating indexes on one or more columns of a table. Indexes are an important database objects that significantly speed up query performance, especially when dealing with large datasets.

This article will guide us through the CREATE INDEX and DROP INDEX statements in SQL with detailed explanations, practical examples, and outputs. We will also discuss unique indexes, multi-column indexes, and scenarios where removing indexes is necessary.

What is an Index in SQL?

An index in SQL is a database objects that improve the speed of data retrieval operations on a table, making queries more efficient. It works similarly to an index in a book, allowing the database engine to find rows quickly based on the values in indexed columns. Without indexes, the database performs a full table scan to retrieve data, which is inefficient for large tables.

Benefits of Indexes

• Faster Query Performance: Indexes reduce the time taken to retrieve data by optimizing lookup paths.
  
• Efficient Filtering: Speeds up operations like WHERE, JOIN, and ORDER BY clauses.
  
• Unique Data Enforcement: Unique indexes ensure no duplicate values in the indexed columns.

CREATE INDEX Statement in SQL

The CREATE INDEX statement in SQL is used to create an index on one or more columns of a table. It can also create unique indexes that enforce the uniqueness of values in the specified columns. They work by providing a quick way to look up rows based on the values in the indexed columns

Syntax:

CREATE INDEX indexname
ON tablename (columnname1, columnname2, ...);

Key Terms

• index_name: The name of the index.
  
• table_name: The name of the table where the index will be created.
  
• column_name(s): The column(s) on which the index is built.

Creating a Unique Index

A unique index in SQL ensures that the values in the indexed columns are distinct, preventing duplicate entries. It is commonly used to enforce data integrity by making sure that no two rows in a table have the same value in the specified columns.

Syntax

CREATE UNIQUE INDEX indexname
ON tablename (columnname1, columnname2, ...);

Example 1: Creating an Index on a Single Column

Let's take an example of a Customer Table where we will create an index on the Country column of the Customer table. This index will help speed up queries that filter or sort based on the Country column, making the data retrieval process more efficient.

1. Create Table Customer

CREATE TABLE Customer(
 CustomerID INT PRIMARY KEY,
 CustomerName VARCHAR(50),
 LastName VARCHAR(50),
 Country VARCHAR(50),
 Age int(2),
 Phone int(10)
);

2. Insert some sample data into the Customers table

INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES 
 (1, 'Shubham', 'Thakur', 'India', 23, 1234567890),
 (2, 'Aman', 'Chopra', 'Australia', 21, 1234567891),
 (3, 'Naveen', 'Tulasi', 'Sri Lanka', 24, 1234567892),
 (4, 'Aditya', 'Arpan', 'Austria', 21, 1234567893),
 (5, 'Nishant', 'Jain', 'Spain', 22, 1234567894);

Output

3. Create Index

CREATE INDEX City
ON Customer(Country);

Note: This code does not produce any output directly visible to the user. When an index is created, the database management system updates its internal data structures to include the new index, but this process does not produce any visible output.

Query:

SELECT * FROM sqlite_master WHERE 
type = 'index' AND tbl_name = 'Customer';

Output

Example 2: Creating an Index on Multiple Columns

In this example, we will create an index on multiple column Country and Age to improve query performance when filtering or sorting data based on both columns. This composite index allows the database to quickly retrieve rows that match specific combinations of values in the Country and Age columns, optimizing queries that involve both fields.

Syntax

CREATE INDEX idx_Customer
_Country_Age ON Customer (Country, Age);

Example

SELECT * 
FROM Customer
WHERE Country = 'India' AND Age = 23;

Output

Explanation:

This query filters the Customer table for records where the Country is 'India' and the Age is 23, returning the row with CustomerID = 1.

DROP INDEX in SQL

The DROP INDEX statement in SQL is used to remove an existing index from a table. Indexes are database objects that improve the speed of data retrieval operations by providing quick access paths to rows in a table.
However, there are situations where an index may no longer be needed or could be causing performance issues, in which case we might want to drop it.

Syntax

DROP INDEX tablename.indexname;

Query:

DROP INDEX City;

Output

Explanation:

The index is deleted, and the database reverts to performing full table scans for queries involving the Country column.

Conclusion

The CREATE INDEX statement is an essential tool for optimizing query performance in SQL. Whether we need a standard or unique index, it provides quick lookup paths for efficient data retrieval. However, it’s equally important to understand when to remove unused or inefficient indexes using the DROP INDEX statement. By carefully designing and managing indexes, we can significantly improve the performance and scalability of our database.