Optimizing Database Queries: From Slow to Fast

Database performance can make or break your application. Let's explore practical techniques for optimizing queries.

Identifying Slow Queries

First, you need to find the bottlenecks. Most databases provide query analysis tools:

-- PostgreSQL: Enable query timing
EXPLAIN ANALYZE
SELECT * FROM users
WHERE created_at > '2024-01-01'
ORDER BY created_at DESC;

Indexing Strategies

Indexes are your first line of defense against slow queries:

-- Create index on frequently queried columns
CREATE INDEX idx_users_created_at ON users(created_at);

-- Composite index for multiple columns
CREATE INDEX idx_users_email_status ON users(email, status);

-- Partial index for specific conditions
CREATE INDEX idx_active_users ON users(id) WHERE status = 'active';

N+1 Query Problem

One of the most common performance issues:

// Bad: N+1 queries
const users = await db.users.findMany();
for (const user of users) {
  const posts = await db.posts.findMany({ where: { userId: user.id } });
}

// Good: Single query with join
const users = await db.users.findMany({
  include: { posts: true }
});

Query Optimization Techniques

1. Select only needed columns: Don't use SELECT *
2. Use LIMIT: Paginate large result sets
3. Avoid subqueries: Use JOINs when possible
4. Cache frequently accessed data: Use Redis or similar

-- Optimized query
SELECT id, name, email
FROM users
WHERE status = 'active'
LIMIT 100 OFFSET 0;

Connection Pooling

Manage database connections efficiently:

import { Pool } from 'pg';

const pool = new Pool({
  max: 20, // Maximum connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

Conclusion

Database optimization is an iterative process. Monitor your queries, add appropriate indexes, and always measure the impact of your changes.