4 Database Indexes That Speed Up Queries 100x: A SQL Optimization Story

I'll never forget the day my boss called me into his office with that look. You know the one—somewhere between disappointment and genuine concern. "The dashboard is taking 47 seconds to load," he said, pulling up our analytics platform. "Customers are complaining."

My stomach dropped. I'd spent three months building that feature, and now it was basically unusable.

That night, I dove deep into our PostgreSQL logs. What I found changed everything: a single query was scanning 2.3 million rows every single time someone loaded the page. After adding the right index, that same query dropped to 0.3 seconds. A 156x improvement with literally five lines of SQL.

Here's the thing about database indexes—they're not magic, but they might as well be. Most developers I meet either ignore them completely or slap a CREATE INDEX on everything and hope for the best. Both approaches will hurt you.

Today, I'm sharing the four index types that have saved my bacon more times than I can count, complete with real-world scenarios and the exact SQL you need.

Why Your Queries Are Probably Slower Than They Should Be

Before we jump into solutions, let's talk about what's actually happening when your database crawls.

Without indexes, your database performs what's called a sequential scan—it literally reads every single row in your table to find what you need. Imagine searching for a specific page in a 1,000-page book by reading every page from start to finish. That's what your database is doing.

Indexes are like the book's table of contents. They create a separate data structure that points directly to where your data lives, turning that exhaustive search into a quick lookup.

But here's where it gets interesting: not all indexes are created equal, and the wrong index can actually make things worse.

H2: Index Type #1: B-Tree Indexes (Your Everyday Workhorse)

What They Are

B-Tree (Balanced Tree) indexes are the default in almost every database system for good reason—they handle 80% of your use cases brilliantly. They organize data in a sorted tree structure that allows for lightning-fast lookups, range queries, and sorting operations.

When to Use Them

B-Tree indexes shine when you're dealing with:

• Equality comparisons (WHERE user_id = 123)
• Range queries (WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31')
• Sorting operations (ORDER BY last_name)
• Pattern matching with leading wildcards (WHERE email LIKE 'john%')

Real-World Example

Let's say you're building an e-commerce platform. Your orders table has millions of rows, and you need to frequently query orders by customer:

-- The slow query (sequential scan)
SELECT * FROM orders 
WHERE customer_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;

-- Execution time: 8.4 seconds (scanning 3.2M rows)

Now add a B-Tree index:

-- Create the index
CREATE INDEX idx_orders_customer_created 
ON orders(customer_id, created_at DESC);

-- Same query, now blazing fast
SELECT * FROM orders 
WHERE customer_id = 12345 
ORDER BY created_at DESC 
LIMIT 10;

-- Execution time: 0.08 seconds (100x faster!)

The Secret Sauce: Composite Indexes

Notice how I indexed both customer_id and created_at? This is called a composite index, and it's crucial. The order matters:

-- This index helps with:
-- ✅ WHERE customer_id = X
-- ✅ WHERE customer_id = X ORDER BY created_at
-- ✅ WHERE customer_id = X AND created_at > Y

-- But NOT with:
-- ❌ WHERE created_at > Y (only)
-- ❌ ORDER BY created_at (only)

Pro tip: Put the most selective column first (the one that narrows down results the most).

H2: Index Type #2: Partial Indexes (The Space Saver)

What They Are

Partial indexes only index a subset of your table based on a condition. They're smaller, faster to maintain, and perfect for queries that always filter on specific values.

When to Use Them

Use partial indexes when:

• You frequently query a specific subset of data
• Most of your table's rows don't match your query conditions
• You want to save disk space and improve write performance

Real-World Example

In my SaaS application, I had a subscriptions table where 95% of subscriptions were active, but I constantly queried for the 5% that were cancelled or expired:

-- Without partial index: slow
SELECT * FROM subscriptions 
WHERE status IN ('cancelled', 'expired') 
AND expires_at < NOW();

-- Execution time: 3.2 seconds

Here's the fix:

-- Create a partial index for inactive subscriptions only
CREATE INDEX idx_inactive_subscriptions 
ON subscriptions(status, expires_at) 
WHERE status IN ('cancelled', 'expired');

-- Same query, dramatically faster
SELECT * FROM subscriptions 
WHERE status IN ('cancelled', 'expired') 
AND expires_at < NOW();

-- Execution time: 0.04 seconds (80x improvement)

The Numbers

The partial index is 95% smaller because it only indexes 5% of the rows. This means faster queries and faster inserts/updates.

H2: Index Type #3: Covering Indexes (The Query Eliminator)

What They Are

A covering index includes all the columns your query needs, so the database never has to touch the actual table. It's like having a cheat sheet with all the answers already written down.

When to Use Them

Covering indexes are perfect for:

• Frequently-run analytical queries
• Queries that select only a few specific columns
• Situations where you can afford slightly larger indexes for massive speed gains

Real-World Example

I had a user dashboard that displayed basic user info—name, email, and last login. The query was hitting the main users table every time:

-- Original query (table scan required)
SELECT user_id, email, last_name, last_login_at 
FROM users 
WHERE account_type = 'premium' 
ORDER BY last_login_at DESC 
LIMIT 50;

-- Execution time: 1.8 seconds

The solution? Include all needed columns in the index:

-- Create a covering index
CREATE INDEX idx_users_premium_covering 
ON users(account_type, last_login_at DESC) 
INCLUDE (user_id, email, last_name);

-- PostgreSQL 11+ syntax
-- For MySQL, add columns directly:
-- CREATE INDEX idx_users_premium_covering 
-- ON users(account_type, last_login_at, user_id, email, last_name);

-- Same query, now using index-only scan
SELECT user_id, email, last_name, last_login_at 
FROM users 
WHERE account_type = 'premium' 
ORDER BY last_login_at DESC 
LIMIT 50;

-- Execution time: 0.02 seconds (90x faster!)

How to Verify It's Working

Use EXPLAIN ANALYZE to confirm you're getting an index-only scan:

EXPLAIN ANALYZE 
SELECT user_id, email, last_name, last_login_at 
FROM users 
WHERE account_type = 'premium' 
ORDER BY last_login_at DESC 
LIMIT 50;

-- Look for: "Index Only Scan using idx_users_premium_covering"

H2: Index Type #4: Hash Indexes (The Equality Specialist)

What They Are

Hash indexes use a hash function to map keys to locations. They're incredibly fast for exact-match lookups but can't handle range queries or sorting.

When to Use Them

Hash indexes excel at:

• Exact equality comparisons (WHERE token = 'abc123')
• Large tables with unique identifiers
• Situations where you never need range queries or sorting

Important: PostgreSQL only made hash indexes crash-safe in version 10+. MySQL's InnoDB doesn't support them at all (it converts them to B-Tree).

Real-World Example

I built an API authentication system that validated tokens on every request. The api_tokens table had 5 million rows:

-- Original query with B-Tree index
CREATE INDEX idx_tokens_btree ON api_tokens(token);

SELECT user_id, permissions 
FROM api_tokens 
WHERE token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...';

-- Execution time: 0.15 seconds

Switching to a hash index:

-- Create hash index (PostgreSQL)
CREATE INDEX idx_tokens_hash ON api_tokens USING HASH(token);

SELECT user_id, permissions 
FROM api_tokens 
WHERE token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...';

-- Execution time: 0.03 seconds (5x faster)

Hash vs B-Tree: The Comparison

When NOT to Use Hash Indexes

-- ❌ These queries WON'T benefit from hash indexes:
WHERE token LIKE 'eyJ%'
WHERE created_at > '2024-01-01'
ORDER BY token
WHERE token IN ('abc', 'def', 'ghi')

Key Takeaways: Your Index Strategy Checklist

Here's what you need to remember:

• Start with B-Tree indexes for 80% of your queries—they're versatile and reliable
• Use composite indexes with the most selective column first (left-to-right matters!)
• Implement partial indexes when you frequently query a small subset of data (saves space and speeds up writes)
• Create covering indexes for your most critical read-heavy queries (include all SELECT columns)
• Consider hash indexes only for exact-match lookups on unique identifiers (PostgreSQL 10+ only)
• Monitor index usage with pg_stat_user_indexes (PostgreSQL) or sys.dm_db_index_usage_stats (SQL Server)
• Don't over-index—each index slows down INSERT/UPDATE/DELETE operations
• Test with real data—use EXPLAIN ANALYZE to verify your indexes are actually being used

The Index Maintenance Rule

I follow this simple rule: if a query runs more than 100 times per day and takes longer than 100ms, it deserves index optimization. Everything else is premature optimization.

FAQ: Your Burning Index Questions Answered

Q: How many indexes is too many?

A: There's no magic number, but I get nervous when a table has more than 5-7 indexes. Each index adds overhead to write operations. I once inherited a table with 23 indexes—INSERT operations were taking 4 seconds each!

The real answer: monitor your write performance. If INSERTs and UPDATEs are slowing down significantly, audit your indexes. Use this query to find unused indexes in PostgreSQL:

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 
AND indexname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

Drop any index with zero scans that isn't a primary key. I've freed up hundreds of gigabytes this way.

Q: Should I index foreign keys?

A: In most cases, absolutely yes. This is one of the most common performance mistakes I see. Foreign keys are used in JOINs constantly, and without indexes, your database performs full table scans.

-- If you have this relationship:
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    product_id INTEGER REFERENCES products(id)
);

-- You almost always want these indexes:
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_product ON orders(product_id);

Exception: If the foreign key column has very low cardinality (like a status field with only 3 possible values), an index might not help much.

Q: My index isn't being used. What's wrong?

A: This frustrated me for weeks when I first started. Here are the most common culprits:

1. Type mismatch: Your column is INTEGER but you're querying with a string

   -- ❌ Index won't be used
   WHERE user_id = '123'  -- string
   -- ✅ Index will be used
   WHERE user_id = 123    -- integer
   

2. Function on indexed column: Wrapping the column in a function breaks index usage

   -- ❌ Index won't be used
   WHERE LOWER(email) = 'john@example.com'
   -- ✅ Create a functional index
   CREATE INDEX idx_email_lower ON users(LOWER(email));
   

3. Leading wildcard in LIKE: Indexes can't help with patterns that start with %

   -- ❌ Index won't be used
   WHERE email LIKE '%@gmail.com'
   -- ✅ Index will be used
   WHERE email LIKE 'john%'
   

4. Table too small: If your table has fewer than ~1000 rows, the database might decide a sequential scan is faster than using the index. This is actually correct behavior!

Use EXPLAIN ANALYZE to see exactly what's happening:

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

Look for "Index Scan" or "Index Only Scan" in the output. If you see "Seq Scan," your index isn't being used.

Conclusion: From 47 Seconds to 0.3 Seconds

Remember that dashboard I mentioned at the beginning? The one that took 47 seconds to load? Here's what I did:

1. Added a composite B-Tree index on (user_id, created_at) for the main query
2. Created a partial index for filtering active records only
3. Implemented a covering index for the summary statistics panel
4. Switched to a hash index for session token lookups

The result? Load time dropped from 47 seconds to 0.3 seconds. My boss was happy. Customers stopped complaining. I got a bonus.

But more importantly, I learned that database optimization isn't about memorizing syntax—it's about understanding your data access patterns and choosing the right tool for each job.

Start with your slowest queries. Run EXPLAIN ANALYZE. Look at what's actually happening. Then apply these four index types strategically. You don't need to index everything—just the queries that matter.

Your database is probably faster than you think. It's just waiting for you to give it the right indexes.

Now go make something fast. 🚀

---

Want to dive deeper? Check out your database's query planner documentation and start monitoring your slow query logs. The insights you'll gain are worth their weight in gold—or at least in AWS bills saved.