Database Indexing Explained: Why Queries Are Slow

You've written a perfectly correct SQL query. It works in development with 100 rows. You deploy to production with 10 million rows. The query takes 30 seconds. Users are complaining.

Nine times out of ten, the fix is an index.

What an Index Actually Is

Think of a book's index. Instead of reading every page to find "Redis", you look in the index and jump straight to the right pages.

A database index works the same way. Without it, the database reads every row to find matches (called a full table scan). With an index, it jumps straight to the matching rows.

Here's what the performance difference looks like:

Table with 10 million rows, no index:
SELECT * FROM users WHERE email = 'alice@example.com';
Time: 12,340ms (12 seconds) — reads every row
 
Same query with an index on email:
Time: 0.8ms — jumps directly to the row

That's a 15,000× speedup from one CREATE INDEX statement.

How Indexes Work (B-Tree)

Most database indexes use a B-tree (balanced tree) structure. This is the default when you run CREATE INDEX.

A B-tree keeps data sorted. When you search for a value, the database traverses the tree from root to leaf — at each node, it decides to go left (smaller values) or right (larger values). With 10 million rows, it finds the answer in about 24 comparisons.

10 million rows without index: 10,000,000 comparisons
10 million rows with B-tree:   ~24 comparisons (log₂ of 10M)

-- Add a B-tree index on email column
CREATE INDEX idx_users_email ON users(email);
 
-- Now this query is fast
SELECT * FROM users WHERE email = 'alice@example.com';

When to Add an Index

Add an index when:

• You frequently query by a column in a WHERE clause
• You JOIN tables on a column
• You ORDER BY or GROUP BY a column

-- Slow: full table scan
SELECT * FROM orders WHERE customer_id = 123;
 
-- Add this index
CREATE INDEX idx_orders_customer ON orders(customer_id);
 
-- Now fast
SELECT * FROM orders WHERE customer_id = 123;

Primary keys are automatically indexed. Foreign keys should always be indexed — this is easy to forget and can cause extremely slow JOINs.

Composite Indexes: Indexing Multiple Columns

Sometimes you need to query by multiple columns. A composite index covers multiple columns.

-- Slow: queries users by country AND status
SELECT * FROM users WHERE country = 'IN' AND status = 'active';
 
-- Create a composite index
CREATE INDEX idx_users_country_status ON users(country, status);

Column order matters in composite indexes. The index is useful for queries that use the leftmost columns:

-- Uses the index (country is leftmost)
SELECT * FROM users WHERE country = 'IN' AND status = 'active';
SELECT * FROM users WHERE country = 'IN';
 
-- Does NOT use the index (skips country)
SELECT * FROM users WHERE status = 'active';

Think of it like a phone book sorted by last name then first name. You can find all "Sharma" entries easily, and all "Sharma, Akash" entries. But you can't quickly find all people named "Akash" without scanning the whole book.

Indexes That Hurt Performance

Indexes speed up reads but slow down writes. Every INSERT, UPDATE, or DELETE must also update all indexes on that table.

Avoid indexes on:

• Columns you rarely query by
• Low-cardinality columns with few unique values — an index on a boolean is_deleted column (only true/false) often isn't worth it because the database reads half the table anyway
• Tables that are written to heavily with few reads — a logging table shouldn't have many indexes

Don't add indexes blindly. Measure first.

How to Find Slow Queries

PostgreSQL: Use EXPLAIN ANALYZE to see if a query is doing a full table scan.

EXPLAIN ANALYZE 
SELECT * FROM users WHERE email = 'alice@example.com';

Look for Seq Scan (sequential/full scan = bad for large tables). You want to see Index Scan.

MySQL: Same idea — EXPLAIN SELECT ... shows if it's using an index.

Find slow queries automatically:

-- PostgreSQL: find queries taking more than 1 second
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000
ORDER BY mean_exec_time DESC;

Other Index Types

Unique index: Enforces uniqueness. Automatically created by UNIQUE constraint.

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

Partial index: Only indexes rows matching a condition. Smaller and faster.

-- Only index active users (skips deleted/inactive rows)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

Full-text index: For searching text content. Supports LIKE '%keyword%' efficiently.

CREATE INDEX idx_posts_content ON posts USING GIN(to_tsvector('english', content));

Key Takeaways

• Without an index, the database reads every row — fine for small tables, catastrophic for large ones
• B-tree indexes keep data sorted, letting the database find rows in ~24 steps instead of millions
• Column order matters in composite indexes — put the most selective column first
• Indexes speed up reads but slow down writes — don't index everything
• Use EXPLAIN ANALYZE (PostgreSQL) to see if your query is doing a full scan
• Foreign key columns almost always need indexes

The single highest-ROI optimization for most backend applications is adding the right database indexes.

Related reading: Redis Caching Explained · Database Sharding