SQL Indexes Explained

An index is the reason a query on a million-row table can return in milliseconds instead of seconds. Without one, the database reads every row to find what you asked for. With the right index, it jumps straight to the rows like you would flip to a word in a dictionary instead of reading it cover to cover.

Throughout this tutorial we use two tables you will see again across the SQL learning hub: an employees table and an orders table. Everything here works the same in MySQL, PostgreSQL, and SQL Server unless noted.

What an index actually is

Picture the index at the back of a textbook. Instead of scanning every page for the term "recursion", you look it up alphabetically and it tells you the exact pages. A SQL index does the same thing: it is a separate, sorted copy of one or more columns, with a pointer back to the full row.

Most relational databases build indexes as a B-tree (balanced tree). The tree keeps values sorted and balanced so that finding any value takes a small, roughly constant number of steps even as the table grows. Searching a billion sorted entries in a B-tree takes about 30 comparisons, not a billion.

The table itself is called the heap (or, when a clustered index exists, the clustered structure). The index does not replace the table; it sits beside it and speeds up specific lookups.

Pro tip: An index is a trade. You spend extra storage and slower writes to buy faster reads. If a table is read constantly and written rarely, that trade is almost always worth it.

Creating your first index

Here is the schema we will work with. The employees table stores staff, and orders records sales, with emp_id linking an order to the employee who handled it.

CREATE TABLE employees (
    emp_id      INT PRIMARY KEY,
    name        VARCHAR(100),
    department  VARCHAR(50),
    salary      DECIMAL(10, 2),
    hire_date   DATE
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    emp_id      INT REFERENCES employees(emp_id),
    order_date  DATE,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20)
);

Suppose you frequently look up employees by department:

SELECT emp_id, name, salary
FROM employees
WHERE department = 'Engineering';

Without an index on department, the database performs a full table scan — it reads every row and checks the department. On a large table that is slow. Add an index and the pattern changes:

CREATE INDEX idx_employees_department
ON employees (department);

Now the same query does an index seek: it walks the B-tree to the "Engineering" entries and follows the pointers to just those rows. The PRIMARY KEY on emp_id, by the way, already created an index for you automatically — that is why primary key lookups are always fast.

How the database decides to use an index

You do not tell the database to use an index; the query optimizer decides. It estimates the cost of each plan and picks the cheapest. Two things drive its choice: selectivity and statistics.

Selectivity is how many rows a filter eliminates. A filter on status = 'CANCELLED' when only 2% of orders are cancelled is highly selective — an index is a big win. A filter on status = 'COMPLETED' when 90% of orders are completed is not selective, and the optimizer may correctly choose a full scan because reading almost the whole table through an index is slower than reading it directly.

You inspect the optimizer's choice with EXPLAIN:

EXPLAIN
SELECT emp_id, name
FROM employees
WHERE department = 'Engineering';

Read the output for the access method. Words like Index Seek, Index Scan, or Using index mean your index is working. Words like Seq Scan (PostgreSQL) or type: ALL (MySQL) mean a full table scan. Learning to read these plans is one of the highest-value SQL skills you can build, and it shows up constantly in the SQL interview questions freshers get asked.

Composite indexes and column order

An index can cover more than one column. These are composite (or compound) indexes, and column order matters enormously.

CREATE INDEX idx_orders_emp_date
ON orders (emp_id, order_date);

This index is sorted by emp_id first, then by order_date within each employee. It serves queries that filter on emp_id alone, or on emp_id and order_date together:

SELECT order_id, amount
FROM orders
WHERE emp_id = 42
  AND order_date >= '2026-01-01';

But it does not help a query that filters only on order_date, because the index is sorted by emp_id first. This is the leftmost prefix rule: a composite index can be used only if the query uses a continuous prefix of its columns starting from the left.

Interview note: "Given an index on (a, b, c), which of these WHERE clauses can use it?" is a classic. The answer: filters using a, or a+b, or a+b+c can use it; a filter using only b or only c cannot. Practice explaining why out loud.

Common mistakes that stop an index from being used

Indexes fail silently. The query still returns correct results, just slowly, so the problem hides until the table grows. Watch for these.

Wrapping the column in a function. The index stores raw column values, not transformed ones:

-- Index on hire_date is IGNORED here
SELECT name FROM employees
WHERE YEAR(hire_date) = 2025;

-- Rewrite so the column stays bare — index is used
SELECT name FROM employees
WHERE hire_date >= '2025-01-01'
  AND hire_date <  '2026-01-01';

Leading wildcards. WHERE name LIKE '%rao' cannot use a normal index because the index is sorted from the start of the string. WHERE name LIKE 'rao%' can.

Data type mismatches. Comparing an indexed VARCHAR column to a number forces an implicit conversion that disables the index. Match the types.

Over-indexing. Every index must be updated on every insert, update, and delete. Ten indexes on a write-heavy orders table can make inserts noticeably slower. Keep the indexes that earn their place and drop the rest.

Common mistake: Adding an index on a column with very few distinct values, like a status column with three possible values or a boolean flag. Low-cardinality columns rarely benefit from a standard B-tree index because filters on them are not selective.

Clustered vs non-clustered — the one physical index

There are two broad kinds of index and the difference is about physical storage.

A clustered index determines the physical order of the rows on disk. Because rows can only be stored in one order, a table has at most one clustered index — typically the primary key. Range queries on the clustered key are extremely fast because the matching rows sit next to each other.

A non-clustered index is a separate structure holding the indexed columns plus a pointer (the primary key or a row locator) back to the full row. You can create many of these. When the database uses one, it finds the value in the index, then follows the pointer to fetch the rest of the row — a step called a lookup. If a non-clustered index already contains every column the query needs, no lookup is required; that is a covering index, the fastest read pattern there is.

Where indexes fit in real applications

In a full-stack app, indexes are where slow pages get fixed. A dashboard listing recent orders per employee stays fast because of idx_orders_emp_date. A login lookup by email stays fast because of a unique index on the email column. When you build backend services in the Java Full Stack with AI program, tuning these queries with the right indexes is exactly the kind of production work you will do.

Indexes also interact with correctness features you will meet in SQL transactions: a unique index is how the database enforces that no two employees share the same email, even under concurrent inserts. And the JOINs you learn here are dramatically faster when the joined columns are indexed on both sides.

A quick checklist for adding an index

Before you run CREATE INDEX, ask yourself four questions. Is this column used in a WHERE, JOIN, or ORDER BY that runs often? Is the filter selective enough to eliminate most rows? Is the table read more than it is written? Have you confirmed with EXPLAIN that the current query does a full scan? If the answer to all four is yes, add the index and re-check the plan.

Indexing is not about adding as many as possible. It is about adding the few that turn your slowest queries into fast ones, then measuring to prove it. Master that loop — measure, index, measure again — and you will handle the performance questions that separate confident SQL developers from the rest.