Member-only story
How Database Indexes Work (In Simple Words)
When you run a database query, you want results as fast as possible. But without optimization, databases often need to scan every row to find what you’re looking for. This process can be painfully slow, especially for large tables.
Indexes can help with that. They work like a table of contents in a book — helping the database jump directly to the right place instead of flipping through every page.
Imagine you have a users table with 1 million records, and you need to find a user by email:
SELECT * FROM users WHERE email = 'alice@email.com';Without an Index (Slow Query)
- The database starts from the first page and checks every row, one by one.
- If the record is stored towards the end, the database has to scan the entire table before finding it.
This is called a full table scan. It is slow because databases store data in pages.
What is a Page in a Database?
A page is a fixed-size block of memory (usually 8 KB or 16 KB) that holds multiple rows.
When a database fetches data from disk, it does not retrieve a single row — it loads an entire page into memory.
