SQL Indexes

In SQL, an index is a database object that improves the speed of data retrieval from a table. It works similarly to an index in a book — instead of scanning the whole table, the database uses the index to find data faster.

Without an index, SQL must read each row in a table to find the required data (this is called a full table scan). With an index, SQL can quickly jump to the matching rows.

---

Why Use an Index?

Indexes help in:

• Speeding up SELECT queries.
• Quickly locating data without scanning the entire table.
• Improving sorting and filtering performance.

However, indexes also:

• Take up additional storage.
• Slightly slow down INSERT, UPDATE, and DELETE operations because the index must also be updated.

---

How Does an Index Work?

Internally, most SQL indexes use a B-tree structure or hashing to store values in a sorted manner.
When you search for a value, SQL traverses the tree or hash map to directly reach the row, rather than reading all rows.

Example:
Without an index, finding WHERE name = 'Riya' in a table with 1 million rows may require checking each row.
With an index on name, SQL can directly go to the matching record in milliseconds.

---

Types of Indexes in SQL

1. Primary Index

• Automatically created when you define a PRIMARY KEY.
• Ensures uniqueness of values.
• Example:

CREATE TABLE Students (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50)
);

Here, RollNo has a primary index.

---

2. Unique Index

• Ensures all values in the indexed column are unique.
• Example:

CREATE UNIQUE INDEX idx_email ON Users(Email);

This prevents duplicate email addresses.

---

3. Clustered Index

• Sorts and stores the rows in the table based on the index key.
• A table can have only one clustered index.
• Example:

CREATE CLUSTERED INDEX idx_rollno ON Students(RollNo);

---

4. Non-Clustered Index

• Creates a separate structure that stores column values and a pointer to the actual row.
• A table can have multiple non-clustered indexes.
• Example:

CREATE INDEX idx_name ON Students(Name);

---

5. Composite Index

• An index on two or more columns.
• Useful when queries often use multiple columns in WHERE or ORDER BY.
• Example:

CREATE INDEX idx_name_city ON Customers(Name, City);

---

6. Full-Text Index

• Used for searching large text columns efficiently.
• Example: Searching keywords in articles or product descriptions.

---

Creating and Dropping Indexes

Create an Index:

CREATE INDEX idx_columnname
ON table_name(column_name);

Drop an Index:

DROP INDEX idx_columnname;

---

Advantages of Indexes

• Faster retrieval of data.
• Improves performance for search, sort, and filter queries.
• Reduces disk I/O operations.

---

Disadvantages of Indexes

• Requires additional storage space.
• Slows down write operations (INSERT, UPDATE, DELETE).
• Needs maintenance when data changes.

---

Best Practices for Using Indexes

1. Index columns used frequently in WHERE, JOIN, ORDER BY, and GROUP BY.
2. Avoid indexing small tables — a full scan may be faster.
3. Don’t over-index — it increases storage and maintenance costs.
4. Use composite indexes wisely — order of columns matters.

---

Example: Without and With Index

Without Index:

SELECT * FROM Students WHERE Name = 'Riya';

SQL scans the entire table.

With Index:

CREATE INDEX idx_name ON Students(Name);
SELECT * FROM Students WHERE Name = 'Riya';

SQL quickly finds the matching record.

---

Conclusion

SQL indexes are powerful tools to improve query performance. By storing data in an organized structure, they allow databases to retrieve information quickly. However, indexes must be used wisely — too many can slow down write operations and consume storage. The right balance ensures optimal performance.