Why Your Queries Take Forever
I've optimized databases that went from 12-second queries to 50-millisecond queries. And in almost every case, the problem wasn't the database - it was how we were using it.
The N+1 Query Problem
This is the most common performance killer I see. Here's what it looks like:
// BAD: N+1 queries
$posts = Post::all(); // 1 query
foreach ($posts as $post) {
echo $post->author->name; // N queries!
}
// GOOD: Eager loading
$posts = Post::with('author')->get(); // 2 queries total
That simple change can turn 101 queries into 2. I've seen it reduce page load times from 8 seconds to 200ms.
Indexing: The Single Biggest Win
Adding the right index to a table can make a query 100x faster. But most developers either:
- Don't add indexes at all
- Add indexes on every column (which slows down writes)
Rules for indexing:
1. Index columns you frequently filter or sort by
2. Index foreign key columns
3. Use composite indexes for queries that filter on multiple columns
4. Don't index columns with low cardinality (like boolean fields)
Query Optimization Techniques
1. Select only what you need
-- BAD
SELECT * FROM users;
-- GOOD
SELECT id, name, email FROM users;
2. Use EXPLAIN to understand your queries
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;
3. Avoid functions on indexed columns
-- BAD (can't use index)
WHERE YEAR(created_at) = 2026
-- GOOD (can use index)
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-01'
Connection Pooling
Opening a new database connection for every request is expensive. Use connection pooling:
- PgBouncer for PostgreSQL
- Built-in pooling in most ORMs
Caching Strategy
Not every request needs to hit the database.
- Application-level cache: Redis or Memcached for frequently accessed data
- Query result caching: Cache expensive query results for 5-60 minutes
- HTTP caching: Use ETags and Cache-Control headers
When to Denormalize
Normalization is great for data integrity, but sometimes you need to denormalize for performance:
- Counters: Store comment_count on posts instead of counting every time
- Aggregates: Pre-calculate totals for dashboards
- Search data: Create denormalized search tables for complex queries
Monitoring Your Database
You can't fix what you can't see:
- Enable slow query logging
- Monitor connection counts
- Track query execution times
- Set up alerts for unusual patterns
A well-optimized database is the foundation of every fast application.
