Indexes in SQL

Indexes in SQL

In SQL, an index is a database object that improves the speed of data retrieval (SELECT queries) from a table.

Think of an index like the index of a book: instead of reading the whole book to find a topic, you can go directly to the indexed page. Similarly, indexes help the database quickly locate rows without scanning the entire table.

✅ Types of Indexes

  1. Single-column index → Index created on one column.
  2. Composite index → Index created on multiple columns.
  3. Unique index → Ensures all values in the indexed column(s) are unique.
  4. Clustered index → Reorders the actual table rows to match the index (only one per table).
  5. Non-clustered index → Stores a pointer to the actual data (can have multiple per table).

📌 Example

1. Create a Table

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(50),
    Salary DECIMAL(10,2)
);

2. Create an Index

-- Index on the Department column
CREATE INDEX idx_department
ON Employees (Department);

This helps speed up queries filtering by Department.

3. Use the Index in a Query

SELECT Name, Salary
FROM Employees
WHERE Department = 'IT';

Without an index, SQL scans all rows (full table scan). With idx_department, it quickly finds matching rows.

4. Composite Index Example

CREATE INDEX idx_dept_salary
ON Employees (Department, Salary);

This index is useful when you often query using both Department and Salary together.

⚠️ Notes

  • Indexes improve read performance but can slow down write operations (INSERT, UPDATE, DELETE), because the index also needs updating.
  • Too many indexes can hurt performance and take up extra storage.

Leave a Reply