What Is a Database Index? Explained Simply, with a Demo

In this chapter

What an index is, and why a database needs one

Database indexes: common questions

What is an index in a database?

A separate structure that keeps the values of one column (or a few) in sorted order, each pointing to its row. The database can jump to a value instead of reading the whole table.

Why does an index make queries faster?

Without one, finding a value means checking every row: a full table scan. A sorted index lets the database discard half of what is left at each step, so a million rows take about 20 checks instead of a million.

What is the downside of an index?

Every INSERT, UPDATE and DELETE must also update every index on the table, and each index uses disk and memory. Add indexes for columns you filter, join or sort on often, not for every column.

Keep learning

• Previous: Row Store vs Column Store Database: Differences
• Next: 30 Database Index Interview Questions to Test Yourself
• All SQL tutorial chapters

Overview

Lab: find one email, with and without an index Email to find Without an index: check every row in turn · found With an index on email: jump to the middle, go left or right ▶ Next step ⏭ Run to the end ↺ Reset = total ? ( Lab: make the table bigger chapters · indexes What an index is, and why a database needs one Before clustered keys and B-trees, the basic idea: an index is a sorted list of one column that points back to the rows. It turns “read the whole table” into “jump straight there”. This chapter shows why that works, how you make one, what it costs, and where the next chapters go deeper.

1. The problem: a table is not stored in any useful order

Take a users table. New users are added at the end as they sign up, so the rows sit in sign-up order. Nothing in that order helps you find an email. Now a user logs in and the app runs: The database has no idea where Priya’s row is. The only safe thing it can do is start at row 1 and check every row until the end (it can’t stop early, because it doesn’t know another row won’t also match). This is called a full table scan. On 16 rows that’s nothing. On 10 million rows, for every single login, it is a disaster.

2. The idea: the index at the back of a book

A textbook has the same problem. To find “photosynthesis” you don’t read all 400 pages. You flip to the index at the back, where topics are in alphabetical order, find the word, and read the page number: photosynthesis → page 112. Then you jump to page 112. A database index is exactly that, for one column: A separate list The index is stored next to the table, not inside it. The table itself does not move. Sorted by one column An index on email keeps every email in alphabetical order. Sorted is the whole trick. Each entry points to its row Next to each email sits where its row lives, like the page number in a book. Why does sorted help so much? Because you can jump to the middle, see whether your email comes before or after, and throw away half the list in one go. Then do it again with the half that is left. Step through it:

3. Why it matters more as the table grows

With 16 rows, 16 checks versus 4 doesn’t matter. The difference is how each one grows. The scan checks every row, so its work grows with the table. Halving only needs one more check each time the table doubles. Slide the size up: That is why an index is usually the first fix for a slow query, long before a bigger server or sharding.

4. How you make one, and how the database uses it

One line creates the index. The database fills it from the existing rows and then keeps it up to date by itself: To check that the planner really used it, put EXPLAIN in front of the query: Before the index After the index

5. What a sorted list can and can’t help with

Because the index is just the column in sorted order, it helps any question that sorted order answers, and nothing else: Index on email helps? Jump to one spot in the sorted list. WHERE email LIKE 'pri%' Everything starting with “pri” sits together in sorted order. ORDER BY email LIMIT 20 WHERE email LIKE '%mail.com' Words ending in “mail.com” are spread all over an alphabetical list. The list is sorted by email, not by LOWER(email). This index sorts emails. Names need their own index. Keep this table in mind: almost every “why isn’t my index used?” question comes back to it.

6. The price: every index slows down writes

An index is a second copy of the column that must stay sorted. So when a row is added, changed or deleted, the database updates the table and every index on it: INSERT INTO users … write the row the table add to idx_users_email in sorted position add to idx_users_name Five indexes means six writes for every new user. Indexes also take disk space and memory. Add an index when A query you run often filters, joins or sorts on that column, and the table is big enough that scanning hurts. Skip it when Nobody searches by that column, the table is tiny, or the table is written far more than it is read (like a log).

7. Where the next chapters go

Everything above is the idea. The next chapters answer the questions it leaves open, in this order: Index types: does the index hold the whole row or only a pointer (clustered vs non-clustered), and what do “unique”, “composite” and “covering” mean? B-trees: a plain sorted list is slow to insert into, since everything after the new entry must shift. Real databases keep the sorted list in a tree of pages instead. B-tree height: why 100 million rows still take only about three page reads. How rows are stored and why primary key order matters. Then how queries use indexes: the cost of the jump back to the row, covering indexes, column order in multi-column indexes, ranges, and the cases above where the index is skipped. Want to see how much you already know? Try the thirty index questions now, and again after the module.

More courses

• Jobs, linked to lessons — Browse jobs where every requirement links to the chapter that teaches it. See how ready you are, how long the gap takes to close, and which listings look stale.
• Match a job post to courses — Paste a job post. Each skill is matched to a track here and put in learning order, foundations first.
• React from open source with patterns — Patterns extracted from Material UI, Radix, React Aria, React Hook Form, Redux Toolkit, TanStack, and Next.js — not slides about hooks.
• System Design — Core concepts first (SQL vs NoSQL, CAP, indexing, replication, sharding), then designs grouped by what carries them: the database, Redis, Kafka, or many services.
• Build a real API with NestJS — One ticket-booking API, built in order: a plain Node server first, then NestJS modules, validation, a real database, and the features real clients lean on.
• Microservices — Microservice patterns one diagram at a time: how to split a monolith, how services talk, and how data stays consistent without distributed transactions.
• Kafka — A beginner-to-senior path through event logs, reliable delivery, production operations, and disaster recovery.
• AI engineering for software engineers — Eight free reads, in order, for software engineers moving into AI: when to build an agent, how to build one, how to test it, and how the model works underneath.
• Frontend system design — How a UI is sliced when it is a product, not a page: ownership of state, cache, routes, and what happens when the network lies.
• TypeScript in depth — The type system as a design tool. Inference, narrowing, generics, and the errors that mean the model is wrong — not a catalog of utility types.
• CSS in depth — See the box, then the page. Visual chapters from the box model through Flexbox surprises, Grid, selectors, and units, then backgrounds, shapes, and visual effects, each with a playground.
• Accessibility — Foundations first (WCAG in plain words, semantic HTML, lists, alt text), then hands-on HTML labs, a full pass of WAI-ARIA 1.3, and a final fix-the-page challenge.
• Docker — What an image is, why layers cache, and how containers find each other on a network.
• Redis — Fifteen Redis patterns, one picture each: cache, session, lock, counter, rate limiter, queue, and when Redis is the wrong tool.
• Kubernetes — Kubernetes from zero to operators: why it exists, Pods, Deployments and Services, then configuration, networking, storage, scheduling, scaling and security.