What an Index Really Is

How a query actually finds rows

Run SELECT * FROM users WHERE email = 'ben@example.com' on a plain table and the database does the only thing it can: read every row and test each one. That is a full table scan. With 3 rows it is instant. With 50 million rows it reads 50 million rows to find one, and it does that on every such query.

An index fixes this. An index on users(email) is a separate structure the database maintains alongside the table: all the email values, kept in sorted order, each with a pointer to its row. Sorted order is the entire trick: to find one email among 50 million, the database can check the middle value and instantly discard the half of the list the target cannot be in, then repeat, 50 million → 25 million → 12.5 million ... reaching one row in about 26 steps instead of 50 million. A phone book gives you the same power: because it is sorted by name, you jump near the right spot and narrow down instead of reading every page. In practice indexes are stored as B-trees, wide trees a few levels deep, so a lookup is 3-4 hops even for huge tables.

Making one, and seeing the difference

Creating an index is one statement:

CREATE INDEX idx_users_email ON users(email);

How do you know the database uses it? Put EXPLAIN QUERY PLAN in front of a query and SQLite tells you its plan without running it. Read the plan for one keyword:

• SCAN users: full table scan, reads everything.
• SEARCH users USING INDEX idx_users_email: jumps through the index. This is the fast one.

The next block shows the same query planned before and after creating the index. (The exact wording of plans varies between SQLite versions, so this block has no pass/fail check. You are just reading.)