Database Optimization: From Query Tuning to Sharding - The Complete Guide

The Slow Query That Brought Down Production

Understanding Why Databases Slow Down

Database optimization diagram 1

Query Optimization: The Foundation

The EXPLAIN Command: Your Diagnostic Tool

sql
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 12345
AND created_at > '2024-01-01';

• Scan type: Sequential scan (bad for large tables) vs Index scan (good)
• Rows examined: How many rows the database looked at
• Execution time: Actual time spent

Select Only What You Need

sql
-- Bad: Fetches all columns, prevents covering indexes
SELECT * FROM users WHERE status = 'active';

-- Good: Only fetches needed columns, can use covering index
SELECT id, name, email FROM users WHERE status = 'active';

Write Queries That Use Indexes

sql
-- Cannot use index on created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Can use index on created_at
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

sql
-- If user_id is integer, this may not use index
SELECT * FROM orders WHERE user_id = '12345';

-- Correct type, uses index
SELECT * FROM orders WHERE user_id = 12345;

sql
-- Cannot use index (starts with wildcard)
SELECT * FROM products WHERE name LIKE '%phone%';

-- Can use index (wildcard at end only)
SELECT * FROM products WHERE name LIKE 'phone%';

sql
-- May not use indexes efficiently
SELECT * FROM users WHERE email = 'test@example.com' OR phone = '555-1234';

-- Better: Use UNION for each condition
SELECT * FROM users WHERE email = 'test@example.com'
UNION
SELECT * FROM users WHERE phone = '555-1234';

Avoid N+1 Query Problems

go
// Bad: N+1 queries (1 for orders + N for customers)
orders := db.Query("SELECT * FROM orders LIMIT 100")
for _, order := range orders {
    customer := db.Query("SELECT * FROM customers WHERE id = ?", order.CustomerID)
    // process...
}

// Good: 2 queries total using JOIN or IN clause
orders := db.Query(`
    SELECT o.*, c.name, c.email
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    LIMIT 100
`)

Indexing Strategies: The Art of Fast Lookups

How Indexes Work

Database optimization diagram 2

Types of Indexes

sql
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(created_at);

sql
-- Only useful for: WHERE email = 'exact@match.com'
-- Cannot help with: WHERE email LIKE 'john%' or ORDER BY email
CREATE INDEX idx_users_email ON users USING HASH (email);

sql
-- Order matters! This index helps queries that filter by customer_id,
-- or customer_id AND status, or customer_id AND status AND created_at.
-- It does NOT help queries filtering only by status or only by created_at.
CREATE INDEX idx_orders_composite ON orders(customer_id, status, created_at);

sql
-- If most queries only need id, status, and amount:
CREATE INDEX idx_orders_covering ON orders(customer_id, status, created_at)
INCLUDE (id, amount);

-- This query can be satisfied entirely from the index:
SELECT id, status, amount FROM orders
WHERE customer_id = 123 AND status = 'pending';

sql
-- Only index active users (maybe 10% of total)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

When NOT to Index

• Slows down INSERT, UPDATE, DELETE operations
• Consumes storage space
• Requires maintenance during data modifications

1. Low cardinality columns: A boolean column with only true/false values won't benefit much from indexing. The database might scan half the table anyway.
2. Frequently updated columns: If a column changes constantly, the index maintenance overhead may exceed the query benefit.
3. Small tables: Tables with fewer than a few thousand rows often perform better with full scans than index lookups.
4. Write-heavy workloads: If you write 1000 times for every read, indexes hurt more than help.

Database optimization diagram 3

Caching: The Fastest Query Is No Query

Caching Layers

go
// Simple in-memory cache
var userCache = make(map[int]*User)
var cacheMutex sync.RWMutex

func GetUser(id int) (*User, error) {
    // Check cache first
    cacheMutex.RLock()
    if user, ok := userCache[id]; ok {
        cacheMutex.RUnlock()
        return user, nil
    }
    cacheMutex.RUnlock()

    // Cache miss: query database
    user, err := db.QueryUser(id)
    if err != nil {
        return nil, err
    }

    // Store in cache
    cacheMutex.Lock()
    userCache[id] = user
    cacheMutex.Unlock()

    return user, nil
}

Cache Invalidation Strategies

go
// Set with 5 minute expiration
redis.Set("user:123", userData, 5*time.Minute)

go
func UpdateUser(user *User) error {
    // Update database
    err := db.UpdateUser(user)
    if err != nil {
        return err
    }

    // Update cache immediately
    redis.Set(fmt.Sprintf("user:%d", user.ID), user, 0)
    return nil
}

Database optimization diagram 4

When to Cache

• Data is read frequently, written rarely
• Slight staleness is acceptable
• Computation to generate data is expensive
• Database is a bottleneck

• Data must always be current (financial transactions)
• Data is unique per request (already cacheable by nature)
• Write frequency exceeds read frequency

Replication: Read Scaling and High Availability

Primary-Replica Architecture

Database optimization diagram 5

• Read capacity scales horizontally (add more replicas)
• Primary failure doesn't lose data (promote a replica)
• Replicas can serve different purposes (reporting, analytics)

• Replication lag means replicas may have stale data
• Writes don't scale (still limited by primary)
• Complexity in handling failover and split-brain scenarios

Synchronous vs Asynchronous Replication

Write Request → Primary → Replica Confirms → Response to Client

Write Request → Primary → Response to Client
                     ↓
              Replica (eventually)

Handling Replication Lag

go
func GetOrder(orderID int, justCreated bool) (*Order, error) {
    if justCreated {
        return db.Primary.Query("SELECT * FROM orders WHERE id = ?", orderID)
    }
    return db.Replica.Query("SELECT * FROM orders WHERE id = ?", orderID)
}

Partitioning: Dividing Data for Manageability

Types of Partitioning

sql
-- Partition orders by year
CREATE TABLE orders (
    id BIGINT,
    customer_id INT,
    created_at TIMESTAMP,
    amount DECIMAL
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

sql
-- Partition by region
CREATE TABLE customers (
    id INT,
    name VARCHAR(100),
    region VARCHAR(20)
) PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('NY', 'MA', 'CT'),
    PARTITION p_south VALUES IN ('FL', 'GA', 'TX'),
    PARTITION p_west VALUES IN ('CA', 'WA', 'OR')
);

sql
-- Distribute users evenly across 8 partitions
CREATE TABLE users (
    id INT,
    email VARCHAR(100)
) PARTITION BY HASH (id) PARTITIONS 8;

When to Partition

• Tables exceed hundreds of millions of rows
• You need to efficiently delete old data
• Queries naturally filter by partition key
• Maintenance operations (backups, index rebuilds) take too long

• Tables are small enough to manage as single units
• Queries frequently span all partitions
• You'd create thousands of tiny partitions

Sharding: Horizontal Scaling for Massive Data

Sharding Strategies

Shard 1: user_id 1 - 1,000,000
Shard 2: user_id 1,000,001 - 2,000,000
Shard 3: user_id 2,000,001 - 3,000,000

go
func GetShard(userID int, numShards int) int {
    return hash(userID) % numShards
}

user_id 123 → Shard 2
user_id 456 → Shard 1
user_id 789 → Shard 3

US users → US data center
EU users → EU data center
Asia users → Asia data center

Database optimization diagram 6

Sharding Challenges

sql
-- If users and orders are on different shards, this is painful
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

When to Shard

• Single database can't handle write volume
• Data size exceeds single server storage
• You need geographic distribution
• Vertical scaling (bigger server) is cost-prohibitive

• Read replicas can handle your load
• Partitioning solves your problem
• Your data fits on one server with room to grow
• You haven't exhausted simpler optimizations

SQL vs NoSQL: Choosing the Right Database

When to Choose SQL (Relational Databases)

When to Choose NoSQL

json
{
    "type": "laptop",
    "brand": "Apple",
    "specs": {
        "cpu": "M2",
        "ram": "16GB",
        "storage": "512GB"
    },
    "reviews": [...]
}

session:abc123 → {user_id: 456, expires: 1709251200}
ratelimit:user:789 → 47

Database optimization diagram 7

Scaling SQL Databases

1. Read Replicas: Scale reads horizontally
2. Connection Pooling: Handle more concurrent connections
3. Partitioning: Manage large tables
4. Sharding: Scale writes (with significant complexity)
5. NewSQL: Databases like CockroachDB, TiDB provide SQL interface with horizontal scaling

Scaling NoSQL Databases

1. Built-in Sharding: Most NoSQL databases shard automatically
2. Eventual Consistency: Trade consistency for availability and partition tolerance
3. Denormalization: Store data redundantly to avoid joins
4. Horizontal Scaling: Add nodes to increase capacity

The Optimization Roadmap

Database optimization diagram 8

• Use EXPLAIN to understand query execution
• Select only needed columns
• Eliminate N+1 queries
• Cost: Low. Benefit: Often 10-100x improvement.

• Add indexes for common query patterns
• Create composite indexes for multi-column filters
• Remove unused indexes
• Cost: Low-Medium. Benefit: 10-1000x for specific queries.

• Cache frequently accessed, rarely changing data
• Implement proper invalidation strategies
• Cost: Medium. Benefit: 10-100x for cached data.

• Offload read traffic from primary
• Handle replication lag in application
• Cost: Medium-High. Benefit: Linear scaling of reads.

• Split large tables into manageable pieces
• Align partitions with query patterns
• Cost: Medium. Benefit: Better maintenance, faster queries on partition key.

• Distribute data across multiple servers
• Significant application changes required
• Cost: Very High. Benefit: Horizontal scaling of writes and storage.

Common Mistakes to Avoid

What You Learned

• Query optimization eliminates wasteful work
• Indexing speeds up data retrieval
• Caching avoids queries entirely
• Replication scales read capacity
• Partitioning manages large tables
• Sharding scales write capacity and storage
• Database selection matches tool to problem