Database Indexes Explained Simply: Why Some Queries Take 1ms and Others Take 10 Seconds

You've probably experienced this. Your app works perfectly during development. Then production arrives. A table grows from: 1,000 rows to: 10,000,000 rows Suddenly: - pages load slowly - APIs time out - users complain - CPUs start screaming The SQL query hasn't changed. So what happened? In many cases, the answer is surprisingly simple: You're missing an index. Index - The Mystery of the Slow Query - What Is a Database Index? - A Real-Life Analogy - How Databases Search Without an Index - How Databases Search With an Index - What Is a B-Tree? - Creating Your First Index - The Performance Difference - Single vs Composite Indexes - The Leftmost Prefix Rule - When Indexes Can Hurt Performance - Common Indexing Mistakes - How to Know if an Index Is Being Used - Indexes in PostgreSQL and MySQL - Real-World Examples - Final Thought 1. The Mystery of the Slow Query Imagine this query: SELECT * FROM users WHERE email = 'john@example.com'; Looks harmless. But if your database contains: 10 million users the database might need to check: Row 1 Row 2 Row 3 ... Row 10,000,000 until it finds the match. That's expensive. 2. What Is a Database Index? A database index is a special data structure that helps the database find data quickly. Think of it as: A shortcut to your data. Instead of scanning every row, the database jumps directly to the relevant records. 3. A Real-Life Analogy Imagine you're looking for: "Distributed Systems" inside a 1,000-page book. Without an Index You start from page 1. Page 1 Page 2 Page 3 ... Page 1000 Eventually you find it. Very slow. With an Index You open the index page: Distributed Systems → Page 742 Done. That's exactly what a database index does. 4. How Databases Search Without an Index Without an index: SELECT * FROM users WHERE email = 'john@example.com'; The database performs: Full Table Scan User 1 User 2 User 3 User 4 ... User 10,000,000 Every row is inspected. This becomes painful as data grows. 5. How Databases Search With an Index With an index: CREATE INDEX idx_users_email ON users(email); The database now maintains a searchable structure. Instead of: Scan everything it can: Jump directly to matching rows Result: Milliseconds instead of seconds 6. What Is a B-Tree? Most database indexes use a structure called a B-Tree. A simplified example: [M] / \ [F] [T] / \ / \ A-D G-L N-S U-Z Instead of searching every record: 1 2 3 4 ... the database repeatedly narrows the search space. Much faster. 7. Creating Your First Index Suppose: CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT, email TEXT ); Create an index: CREATE INDEX idx_users_email ON users(email); Now email lookups become dramatically faster. 8. The Performance Difference Imagine: 10 million users Without index: 2–10 seconds With index: 1–20 milliseconds That's why indexes are often the first thing engineers check when debugging slow queries. 9. Single vs Composite Indexes Single Column Index CREATE INDEX idx_users_email ON users(email); Optimized for: WHERE email = ? Composite Index CREATE INDEX idx_users_country_status ON users(country, status); Optimized for: WHERE country = ? AND status = ? 10. The Leftmost Prefix Rule One of the most important concepts. Given: (country, status) The index can help with: WHERE country = 'US' or: WHERE country = 'US' AND status = 'ACTIVE' But often not: WHERE status = 'ACTIVE' because the search starts from the leftmost column. This catches many developers by surprise. 11. When Indexes Can Hurt Performance Indexes aren't free. Every time you: INSERT UPDATE DELETE the index must also be updated. More indexes means: - more storage - slower writes - higher maintenance cost A common mistake is: Indexing everything. 12. Common Indexing Mistakes Mistake #1 Indexing tiny tables. A table with: 100 rows doesn't need fancy indexing. Mistake #2 Too many indexes. Every write becomes slower. Mistake #3 Wrong column order. (country, status) is not the same as: (status, country) Mistake #4 Never checking query plans. Many developers assume indexes are being used. Sometimes they're not. 13. How to Know if an Index Is Being Used In PostgreSQL: EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com'; You'll see something like: Index Scan or: Seq Scan If you see: Seq Scan the database is scanning rows sequentially. Usually not ideal for large tables. 14. Indexes in PostgreSQL and MySQL Both support: - B-Tree indexes - Unique indexes - Composite indexes PostgreSQL also offers advanced types: - Partial Indexes - Expression Indexes - GIN Indexes - GiST Indexes Useful for: - full-text search - JSON queries - arrays - geospatial data 15. Real-World Examples Login Systems WHERE email = ? Index email. E-Commerce WHERE product_id = ? Index product IDs. Social Media WHERE user_id = ? ORDER BY created_at DESC Use a composite index: (user_id, created_at) Banking WHERE account_number = ? Always indexed. 16. Final Thought Database indexes are one of the highest-impact performance tools in software engineering. They don't change your application code. They don't require more servers. They don't require microservices. Yet they can turn: 10 seconds into: 10 milliseconds Understanding indexes is often the difference between: A database that survives millions of users and A database that collapses under its own growth. And the best part? Most performance problems start with a single question: "Do we have the right index for this query?" Top comments (0)