Database Indexing Explained: What's Actually Happening Under the Hood

Your query is slow. You've checked the code, optimized the ORM, maybe even added caching. But the real fix takes one line of SQL — and it has nothing to do with your application code. The Full Table Scan Problem Without an index, every query does the same thing: scans every single row in the table. Got a million users and you're looking up one by email? The database reads all million rows, checks each one, and returns the match. This is called a full table scan, and it's exactly as bad as it sounds. It scales linearly — double the rows, double the time. At small scale, you won't notice. At 10 million rows, your users definitely will. The Book Analogy (That Actually Holds Up) Think of a database table like a book with no table of contents and no page numbers. Finding a specific paragraph means reading page by page from the beginning. That's a full table scan. An index is the table of contents. It tells you exactly where to look. Instead of reading 500 pages, you check the index, flip to