A table with a million rows and no index on the column you're filtering by means the database checks all one million rows, every time, to answer your query. An index is what avoids that, a separate structure the database maintains specifically to find matching rows quickly, the same way a book's index lets you jump straight to a page instead of reading cover to cover.
Learning Objectives
- Explain what an index does and why it speeds up queries.
- Create an index on a column, and a unique index.
- Read a basic query plan to check whether an index is actually being used.
Query Runs
WHERE last_name = 'Hopper'
No Index
Full table scan, every row checked
With Index
Direct lookup, most rows skipped
Result
Same answer, very different speed
Creating an Index
CREATE INDEX syntax is standard SQL and identical across MySQL, PostgreSQL, SQLite, and SQL Server. A unique index does double duty, speeding up lookups and enforcing uniqueness, which is exactly what a UNIQUE constraint creates behind the scenes anyway.
CREATE INDEX idx_employees_last_name ON employees (last_name);
CREATE UNIQUE INDEX idx_employees_email ON employees (email);
A primary key is automatically indexed by every database covered in this course, you never need to manually create an index on it.
Checking Whether an Index Is Used
Creating an index doesn't guarantee the database will actually use it for a given query. Each database has its own way to show the actual plan it chose.
EXPLAIN SELECT * FROM employees WHERE last_name = 'Hopper';
EXPLAIN ANALYZE SELECT * FROM employees WHERE last_name = 'Hopper';
SET STATISTICS IO ON; SELECT * FROM employees WHERE last_name = 'Hopper';
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE last_name = 'Hopper';
The keyword and exact output format differ, but the goal is the same everywhere: see whether the database used an index lookup (fast) or a full table scan (slow on a large table) to answer the query.
When Indexes Help, and When They Don't
An index is a tradeoff, not a free speedup. It makes reads faster at the cost of making writes slightly slower and using additional storage, which is why databases don't just index every column automatically.
- Columns frequently used in WHERE conditions
- Columns frequently used to JOIN two tables
- Columns frequently used in ORDER BY
- Every INSERT/UPDATE/DELETE has to update every index on that table too, slowing writes
- A column with very few distinct values (like a boolean) rarely benefits much from an index
- Indexing every column "just in case" bloats storage and slows writes for little read benefit
Frequently Asked Questions
Does an index on (first_name, last_name) also speed up a query that only filters by last_name?
Generally no, a multi-column index is most effective when the query's conditions match its columns starting from the left, filtering by first_name alone, or by first_name and last_name together, benefits from this index, but filtering by last_name alone typically doesn't.
Is a primary key the same thing as an index?
A primary key constraint and its automatic index are related but distinct: the constraint enforces uniqueness and NOT NULL, and the database creates an index behind the scenes specifically to enforce and speed up that constraint efficiently.
How many indexes is too many on one table?
There's no fixed number, it depends on how write-heavy the table is. A table that's read constantly and rarely written to can reasonably carry more indexes than one with constant high-volume inserts, where every extra index adds real write overhead.
Common Beginner Mistakes
Adding an index to every column "to be safe"
Each index adds storage and slows down every write to that table, since the index has to be updated too. Index the columns that are actually queried frequently, not every column that exists.
Assuming an index automatically speeds up every query on that column
A query using a function on the column (WHERE LOWER(last_name) = 'hopper') often can't use a plain index on last_name at all, the database would need a special function-based index for that specific expression instead.
Never checking EXPLAIN and assuming an index is being used just because it exists
The database's query optimizer decides at run time whether using an index is actually faster than a full scan, for example, a full scan can sometimes beat an index lookup on a very small table. Confirm with EXPLAIN rather than assuming.
Best Practices
- Index columns that are actually used often in WHERE, JOIN, or ORDER BY, based on real query patterns, not guesswork.
- Check EXPLAIN (or the equivalent for your database) on slow queries before assuming an index will fix them.
- Remember that every index has a write-performance cost, don't add one without a reason to expect it'll earn that cost back in read speed.
- Let a UNIQUE constraint's automatic index do double duty, rather than creating a separate plain index on the same column too.
Interview Questions
Why can an index make writes slower?
Every INSERT, UPDATE, or DELETE that touches an indexed column has to update that index's structure too, not just the table's actual row data. More indexes on a table means more structures to maintain on every write, which is real, measurable overhead.
What's the difference between EXPLAIN and EXPLAIN ANALYZE in PostgreSQL?
EXPLAIN shows the query planner's estimated plan without actually running the query. EXPLAIN ANALYZE actually executes the query and shows real timing and row counts alongside the plan, more accurate, but it has the side effect of actually running the query, worth keeping in mind for a query with side effects like an UPDATE.
A query filtering on WHERE YEAR(hire_date) = 2026 isn't using an index on hire_date. Why, and what's the fix?
Wrapping the column in a function (YEAR(hire_date)) means the database can't directly use a plain index on hire_date, since it would need to evaluate the function on every row's value first. The fix is usually rewriting the condition to compare the raw column directly, like WHERE hire_date >= '2026-01-01' AND hire_date < '2027-01-01', which can use a standard index on hire_date.
Summary
This lesson covered what an index does, creating one with CREATE INDEX, checking whether it's actually used with EXPLAIN (or each database's equivalent), and the real tradeoff indexes make between faster reads and slower writes.
What's Next?
The final lesson in this module covers transactions, grouping multiple statements into one all-or-nothing unit, and the ACID guarantees that make them safe.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.