SQL Indexes Explained

An index is a separate data structure the database maintains alongside a table, built specifically to make lookups fast. Without one, finding a row means scanning the entire table from top to bottom — fine for a few hundred rows, painfully slow once a table has millions. An index works the same way a book's index does: instead of reading every page to find a topic, you jump straight to the right place.

Indexes are one of the single biggest levers for query performance, but they come with real tradeoffs, so understanding when to use one (and when not to) matters as much as knowing the syntax.

Creating an Index

Say you frequently filter employees by department, something like a WHERE clause on that column in nearly every report. Without an index, each of those queries scans the full table:

-- Before: every query like this scans the whole table
SELECT * FROM employees WHERE department = 'Engineering';

-- Create an index on the column being filtered
CREATE INDEX idx_employees_department
ON employees (department);

After the index exists, the database can jump almost directly to the matching rows instead of checking every one. You generally won't see any change in the query's syntax or its result — only in how fast it runs, which becomes dramatic as the table grows.

The Tradeoff: Faster Reads, Slower Writes

An index isn't free. Every time you insert, update, or delete a row, the database also has to update every index that touches the changed columns. A table with five indexes means five extra pieces of bookkeeping on every write. That's why indexes are usually added deliberately, on columns that are actually queried often — not on every column "just in case."

A useful rule of thumb: index columns that show up frequently in WHERE clauses, JOIN conditions, or ORDER BY, especially on large tables. Don't index columns you rarely filter or sort by, and be cautious about over-indexing small tables where a full scan is already fast.

Primary Keys Are Indexed Automatically

You don't need to manually create an index on a primary key — every major database creates one automatically the moment you define the constraint, since primary key lookups (like fetching a single row by its id) are extremely common and need to be fast by default:

CREATE TABLE employees (
    id INT PRIMARY KEY,  -- automatically indexed
    name VARCHAR(100),
    department VARCHAR(50)
);

Foreign key columns, on the other hand, are not always indexed automatically depending on the database — it's worth checking, since joins on an unindexed foreign key can quietly become a performance bottleneck as a table grows.

If you're ever unsure whether an index is actually being used, most databases offer an EXPLAIN command that shows the query plan — a quick way to confirm the database is taking the fast path instead of scanning the whole table.

---