An index is the single easiest way to make a slow query fast, and it is also the concept that trips up more beginners than any other part of SQL. This guide keeps things plain: what an index actually is, why it speeds up reads, how to create one, and when adding one is a mistake. Once the idea clicks, you will start to see why some queries fly and others crawl.
We keep this article conceptual and beginner friendly. If you want the deeper mechanics, the B-tree internals, and exactly why the numbers change, read the companion piece how SQL indexes improve performance after this one. Here we focus on building a solid mental model you can trust.
What this guide covers
The book index analogy
Imagine a 900 page reference book with no index in the back. You want every page that mentions "recursion." Your only option is to start at page one and read every single page, checking each for the word. That is slow, and it gets slower as the book gets thicker.
Now flip to the index at the back of the book. It is an alphabetical list of terms, each followed by the exact page numbers where that term appears. You find "recursion" in seconds, jump straight to pages 212 and 418, and you are done. You never touched the other 898 pages.
A database index works exactly the same way. Without one, the database reads the entire table row by row to find your matches. This full pass is called a full table scan, and it is the book with no index. An index is a separate, sorted structure that lets the database jump straight to the rows it needs. The table is the book; the index is the alphabetical listing in the back.
Hold onto this: an index does not change your data or your results. It is a helper structure the database keeps on the side so it can find rows without reading the whole table. Same rows, same answer, far less work.
What an index is and why reads get faster
Physically, an index is a copy of one or more columns from your table, kept in sorted order, with a pointer back to the full row. Most databases store this as a balanced tree structure called a B-tree, which stays shallow even when the table grows to millions of rows. Because the values are sorted, the database can use the same trick you use in a phone book: open near the middle, decide whether to go left or right, and repeat. Each step throws away half of what is left.
That halving is why indexes feel almost magical. To find one row in a million with a full scan, the database inspects up to a million rows. With a B-tree index it inspects roughly twenty. This behavior is described as O(log n) growth: as the table gets ten times bigger, the lookup cost barely moves. The official PostgreSQL documentation on indexes and the MySQL indexes reference both describe this same tree based approach.
Consider a table of one million users. Compare the work involved in a single lookup by email.
-- Without an index on email, the database must scan every row
SELECT id, name FROM users WHERE email = 'ada@example.com';
-- 1,000,000 rows inspected to find (at most) one match
-- Add an index once, and the same query jumps straight to the row
CREATE INDEX idx_users_email ON users (email);
-- Now ~20 comparisons find the row, no full scan neededThe key point for a beginner: you create the index once, and the database keeps it up to date automatically forever after. Every future query that filters on that column can use it. You do not write different SQL to "use" the index; the database's query planner decides to use it on its own when it helps.
Creating indexes: single, composite, unique
The basic syntax is short and nearly identical across engines. You name the index, name the table, and list the column or columns.
-- 1. Single column index: the everyday workhorse
CREATE INDEX idx_orders_customer ON orders (customer_id);
-- 2. Composite index: one index over several columns, in order
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);
-- 3. Unique index: speeds lookups AND blocks duplicate values
CREATE UNIQUE INDEX idx_users_email ON users (email);Composite indexes and column order
A composite index covers more than one column, and the order you list them in matters a lot. Think of it like sorting a guest list first by last name, then by first name. That ordering helps you find everyone named "Lovelace," and then "Ada Lovelace" within that group. It does not help you find everyone named "Ada" regardless of last name, because first name is the second sort key, not the first.
The practical rule is the leftmost prefix: a composite index on (customer_id, order_date) helps queries that filter on customer_id alone, or on customer_id and order_date together. It generally does not help a query that filters on order_date alone, because the index is not sorted by date first.
-- Uses idx_orders_cust_date: filters the leftmost column first
SELECT * FROM orders
WHERE customer_id = 42 AND order_date >= '2026-01-01';-- Skips the leftmost column, so this index usually cannot help
SELECT * FROM orders WHERE order_date >= '2026-01-01';Unique indexes
A unique index does two jobs at once. It speeds up lookups exactly like a normal index, and it enforces a rule: no two rows may share the same value in that column. If you try to insert a second user with an email that already exists, the database rejects it. Primary keys are backed by a unique index automatically. Reach for a unique index whenever a value must be one of a kind, such as an email, a username, or an order number.
Clustered vs non-clustered indexes
You will meet two words early on, and the difference is simpler than it sounds. It comes down to one question: does the index decide the physical order of the table itself, or does it sit off to the side?
- A clustered index stores the actual table rows in the sorted order of the index. Because the rows live in that order, there can be only one clustered index per table. It is like the book itself being printed in alphabetical order.
- A non-clustered index is a separate structure that holds the sorted values plus a pointer back to the real row. You can have many of these on one table. It is the index in the back of the book pointing at page numbers.
Different engines use these terms differently, so here is a beginner level map rather than a deep dive.
| Engine | What clusters the rows | Extra indexes |
|---|---|---|
| MySQL (InnoDB) | The primary key is always the clustered index | All others are non-clustered (secondary) |
| SQL Server | You choose; the primary key clusters by default | Up to 999 non-clustered indexes |
| PostgreSQL | No permanent clustering; rows sit in a heap | All indexes are non-clustered structures |
As a beginner you rarely set this by hand. Your primary key gives you a clustered index for free in MySQL and SQL Server, and every other index you add is non-clustered. That is enough to reason about performance for now.
The trade-off: reads, writes, and disk
If indexes only made things faster, the database would index everything for you and this article would end here. The catch is that an index is a real structure that has to be stored and maintained. Every time you insert, update, or delete a row, the database must also update every index that touches the affected columns. Reads get faster; writes get a little slower; and the index consumes extra disk space.
| Operation | Effect of adding an index | Why |
|---|---|---|
| SELECT (read) | Much faster | Jumps to matching rows instead of scanning the whole table |
| INSERT | Slightly slower | The new value must be placed into every relevant index |
| UPDATE | Slightly slower | Changing an indexed column means the index entry moves too |
| DELETE | Slightly slower | The row must be removed from the table and every index |
| Disk usage | Higher | Each index is a stored copy of its columns plus pointers |
For most applications this is an easy trade. Typical systems read far more often than they write, so paying a small write penalty to make many reads fast is a clear win. The danger only appears when you overdo it, which brings us to the chart below and the section on when not to index.
These figures are illustrative, not a benchmark of your hardware, but the shape is real and dramatic. On large tables, the difference between an indexed lookup and a full scan is routinely the difference between milliseconds and seconds. That gap is why indexing is the first thing an experienced engineer checks on a slow query, as covered in our SQL performance tuning guide.
Which columns to index
You do not index columns at random. You index the columns the database uses to find and arrange rows. There are three places to look, and they map directly to clauses you already write.
Columns in WHERE
These filter rows. If you regularly search users by email or orders by status, those columns are prime candidates. This is the most common and most valuable reason to index.
Columns in JOIN conditions
Foreign keys like orders.customer_id are joined constantly. Indexing the join column lets the database match rows between tables quickly instead of scanning one table for every row of the other.
Columns in ORDER BY and GROUP BY
Because an index is already sorted, the database can sometimes read rows in the required order straight from the index and skip a separate sorting step entirely.
-- This query touches all three index-worthy places
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id -- JOIN column
WHERE o.status = 'paid' -- WHERE column
ORDER BY o.order_date DESC; -- ORDER BY columnGood candidates here include orders.customer_id, orders.status, and orders.order_date. A single well chosen composite index on (status, order_date) can serve both the filter and the sort at the same time. For a fuller pattern list, see our SQL cheat sheet and the practical guidance in our performance tuning resources.
When NOT to add an index
Indexes are not free, so restraint matters as much as enthusiasm. Skip or reconsider an index in these situations.
- Small tables. If a table has a few hundred rows, a full scan is already instant. The index adds maintenance cost and saves nothing worth measuring.
- Low variety columns. A column that holds only a handful of distinct values, such as a boolean
is_activeor a two optiongenderflag, makes a poor index. Half the table matches "true," so the database may just scan anyway. - Write heavy tables. On a table that receives constant inserts, such as a logging or events table, every extra index slows every write. Index only what you truly query.
- Columns you never filter, join, or sort on. An index on a column that only ever appears in the SELECT list is pure overhead. Index the columns in the WHERE, JOIN, and ORDER BY clauses instead.
Watch out for over-indexing: more indexes is not more speed. Each one slows every insert, update, and delete, eats disk, and gives the query planner more options to sift through. A table drowning in twelve rarely used indexes is often slower overall than the same table with three carefully chosen ones. Add an index to solve a measured problem, not just in case.
Checking if an index is used with EXPLAIN
Creating an index is a suggestion, not a command. The query planner decides whether using it is actually cheaper than a scan. To see what it chose, put the keyword EXPLAIN in front of your query. The database returns its plan instead of running the full query, and the plan tells you whether an index is in play.
-- Ask the database how it intends to run the query
EXPLAIN SELECT id, name FROM users WHERE email = 'ada@example.com';You are looking for one word in the output. A phrase like Index Scan or Index Seek or a named index means your index is being used. A phrase like Seq Scan in PostgreSQL, or type: ALL in MySQL, means a full table scan, which is the warning sign that an index is missing or being ignored.
-- Good: the planner picked the index (fast)
Index Scan using idx_users_email on users
Index Cond: (email = 'ada@example.com')
-- Bad: full scan, index not helping (slow on big tables)
Seq Scan on users
Filter: (email = 'ada@example.com')If you expected an index and see a scan, common causes are wrapping the column in a function, comparing mismatched data types, or a table so small the planner decides a scan is cheaper. We only skim EXPLAIN here; the full walkthrough of reading plans lives in how SQL indexes improve performance. For a deeper, engine neutral treatment of index driven query design, the well regarded resource Use The Index, Luke is worth bookmarking.
Putting it together
Indexes reward a simple, disciplined habit. Start with no extra indexes beyond your primary keys. When a query feels slow, run EXPLAIN, confirm it is doing a full scan, and add an index on the columns in its WHERE, JOIN, or ORDER BY clauses. Re-run EXPLAIN to confirm the index is now used, and measure the improvement. Repeat only where there is a real problem.
That loop, measure, index, verify, keeps you on the winning side of the trade-off: fast reads without a pile of indexes dragging down every write. To keep building, explore our broader notes on SQL indexes and step up your skills with the SQL intermediate course, which puts indexing to work inside real queries.
The one line to remember: an index is the alphabetical listing in the back of the book. It lets the database find rows without reading the whole table, which makes reads fast, costs a little on writes, and should be added on purpose, not everywhere.