SQL Indexes and Query Performance

What is an index?

An index is a data structure that allows the database to find rows faster, at the cost of additional storage and slower writes.

-- Create a basic index
CREATE INDEX idx_employee_email ON employees(email);

-- Unique index (also enforces uniqueness)
CREATE UNIQUE INDEX idx_unique_email ON employees(email);

-- Composite index (column order matters!)
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- Drop an index
DROP INDEX idx_employee_email ON employees;

When indexes help and when they don't

Indexes work best for high-cardinality columns used in WHERE, JOIN, and ORDER BY clauses.

-- INDEX HELPS: equality and range on indexed column
SELECT * FROM employees WHERE email = '[email protected]';
SELECT * FROM employees WHERE salary > 80000;

-- INDEX MAY NOT HELP: function on indexed column
SELECT * FROM employees WHERE LOWER(email) = '[email protected]';
-- Creates a functional index instead:
CREATE INDEX idx_lower_email ON employees(LOWER(email));

-- INDEX DOESN'T HELP: very low cardinality
CREATE INDEX idx_is_active ON employees(is_active);  -- only 2 values
-- Full scan is often faster for boolean columns

Composite index column order

In a composite index, the order of columns matters. The index is usable for queries that start with the leftmost columns.

CREATE INDEX idx_dept_salary ON employees(department, salary);

-- Uses the index (starts from leftmost column):
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM employees WHERE department = 'Eng' AND salary > 80000;

-- Does NOT use the index efficiently:
SELECT * FROM employees WHERE salary > 80000;
-- salary is not the leftmost column

-- Rule of thumb: put equality columns first, range columns last

EXPLAIN and query planning

EXPLAIN shows how the database executes a query, helping identify slow operations.

EXPLAIN SELECT * FROM employees WHERE salary > 80000;

-- Key things to look for in output:
-- type: ALL = full scan (bad), ref/range/const = index used (good)
-- rows: estimated rows scanned
-- key: which index was used (NULL = no index)
-- Extra: "Using filesort", "Using temporary" = potential issues

-- PostgreSQL — more detail:
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 80000;

Exam tip

The most common index interview question: "When would you add an index?" — high-cardinality columns in WHERE/JOIN/ORDER BY. "When would you not?" — boolean/low-cardinality columns, small tables, or tables with heavy writes and few reads.