SQL Indexes

Indexes in SQL are special data structures that improve speed of data retrieval operations. They work like a quick lookup table for the database instead of scanning the entire table row by row, database can use the index to directly locate the required rows.

They play a crucial role in improving performance and efficiency in databases as it:

• Speed up queries (SELECT, JOIN, WHERE, ORDER BY).
• Reduce disk I/O and improve efficiency in large tables.
• Ensure data integrity with unique indexes.
• Must be used wisely as too many indexes can slow down INSERT, UPDATE and DELETE.

Note: Primary Key and Unique constraints automatically create indexes.

1. Creating an Index

There are 3 main ways to create an index in SQL. Let’s look at them one by one.

1.1. Single Column Indexes

A single-column index is created on just one column. It’s the most basic type of index and helps speed up queries when you frequently search, filter or sort by that column.

Syntax:

CREATE INDEX index ON TABLE column;

Let’s create a simple Sales table to understand Indexes:

CREATE TABLE Sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    customer_id INT );

INSERT INTO Sales (sale_id, product_id, quantity, customer_id) VALUES
(1, 101, 5, 201),
(2, 102, 2, 202),
(3, 101, 3, 203),
(4, 103, 1, 204),
(5, 104, 7, 205);

Example:

CREATE INDEX idx_product_id ON Sales (product_id);

Explanation: This creates an index named idx_product_id on the product_id column. Output is usually a confirmation message.

1.2. Multi Column Indexes

A multi-column index is created on two or more columns. It improves performance when queries filter or join based on multiple columns together.

Syntax:

CREATE INDEX index ON TABLE (column1, column2,.....);

Example:

CREATE INDEX idx_product_quantity ON Sales (product_id, quantity);

Explanation: This index allows database to quickly filter or join data based on both product_id and quantity columns with a confirming message.

1.3. Unique Indexes

A unique index ensures that all values in a column (or combination of columns) are unique preventing duplicates and maintaining data integrity.

Syntax:

CREATE UNIQUE INDEX index_name ON table_name (column_name);

Example:

CREATE UNIQUE INDEX idx_unique_employee_id ON Sales (customer_id);

Explanation: This ensures no two rows in the Sales table can have same customer_id.

If we try inserting a duplicate:

INSERT INTO Sales (sale_id, product_id, quantity, customer_id)
VALUES (6, 105, 4, 201);

It will throw an error because customer_id = 201 already exists.

2. Removing an Index

Indexes take up storage and add overhead on write operations (INSERT, UPDATE, DELETE). If an index is no longer needed, it can be removed.

Syntax:

DROP INDEX index_name;

Example:

DROP INDEX idx_product_quantity;

Explanation: Deletes the idx_product_quantity index but keeps the underlying table data intact.

3. Altering an Index

If an index requires adjustments, such as reorganizing or rebuilding, it can be altered without affecting the data. This is useful for optimizing index performance as tables grow larger.

Syntax:

ALTER INDEX IndexName ON TableName REBUILD;

Example:

ALTER INDEX idx_product_id ON Sales REBUILD;

Explanation: Rebuilds the idx_product_id index to optimize its structure.

4. Confirming and Viewing Indexes

We can view all the indexes in a database to understand which ones are in use and confirm their structure. In SQL, the following query helps us see the indexes for a given table:

Syntax:

SHOW INDEXES FROM table_name;

Example:

SHOW INDEXES FROM Sales;

Output

Explanation: Displays all indexes defined on the Sales table.

5. Renaming an Index

In some cases, renaming an index might be necessary for clarity or consistency. While SQL does not directly support renaming indexes, we can use a combination of commands to achieve this.

Syntax:

EXEC sp_rename 'old_index_name', 'new_index_name', 'INDEX';

Example:

EXEC sp_rename 'idx_product_quantity', 'idx_prod_qty', 'INDEX';

Explanation: This command allows us to rename an existing index, which helps maintain clarity in our database schema.