Indexes in SQL

🔹 What is an Index?

An index is a database object that improves the speed of data retrieval by creating a quick lookup structure, similar to a book index.

🔹 Types of Indexes

• Single-column Index — Created on one column.

CREATE INDEX idx_employee_name ON employees(name);

• Composite Index — Created on multiple columns.

CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);

• Unique Index — Ensures all values in the column(s) are unique.

CREATE UNIQUE INDEX idx_email ON users(email);

• Clustered Index — Physically reorders table rows by the index; only one per table.
• Non-clustered Index — Separate structure pointing to table rows; multiple allowed.

🔹 Pros and Cons

🔹 Example Query Using Index

SELECT *
FROM employees
WHERE dept_id = 10 AND salary > 50000;
-- Faster with index on (dept_id, salary)