Case Study: How We Fixed a Chronic Database Bottleneck and Slashed Page Load Times by 73%

A deep dive into how we diagnosed and solved severe performance issues for an e-commerce platform using SQL optimization and caching strategies.

Introduction

We partnered with a growing e-commerce client who was facing a critical problem: their website was becoming unbearably slow during peak traffic. Product pages took over 5 seconds to load, leading to a rising cart abandonment rate and frustrated customers. Their infrastructure costs were also skyrocketing as their cloud provider automatically scaled up their database tier to handle the inefficient load.

The client knew the database was the culprit but didn’t know how to fix it. This case study walks through our diagnostic process and the key solutions we implemented.

The Challenge: Scaling Pains

The platform was built on a standard LAMP (Linux, Apache, MySQL, PHP) stack. The initial database schema, designed for a smaller catalog, couldn’t handle the current load of over 100,000 products and thousands of concurrent users.

The main symptoms were:

• Slow API Responses: Key endpoints, especially /api/products/ and /api/category/, were taking 3-5 seconds.
• High Database CPU: Their MySQL server CPU was consistently above 90% during business hours.
• Poor User Experience: Laggy page loads were directly impacting sales.

Our Investigation: Finding the Root Cause

Our first step was to dive into the database logs and identify the problematic queries. We used MySQL’s Slow Query Log and the EXPLAIN command—a crucial tool for any developer dealing with performance issues.

We immediately identified the main villain: a query used to fetch product data along with its categories and attributes.

SELECT *
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN categories c ON pc.category_id = c.id
LEFT JOIN product_attributes pa ON p.id = pa.product_id
LEFT JOIN attributes a ON pa.attribute_id = a.id
WHERE p.id = 12345;

This query was inefficient because:

• It used SELECT *, fetching unnecessary data.
• It involved multiple joins across large tables.
• It lacked proper indexing on the join columns (product_id, category_id).

Using EXPLAIN confirmed our fears: it was performing full table scans (ALL access type) instead of using indexes.

The Solution: A Multi-Layered Approach

We didn’t rely on a single silver bullet. We implemented a three-part solution.

1. Query Optimization & Indexing

First, we rewrote the query to be specific and efficient. We added strategic indexes to all foreign key columns and frequently queried fields.

SELECT 
    p.id, p.name, p.price, p.description, 
    c.id AS category_id, c.name AS category_name,
    a.id AS attribute_id, a.name AS attribute_name, pa.value
FROM products p
INNER JOIN product_categories pc ON p.id = pc.product_id
INNER JOIN categories c ON pc.category_id = c.id
LEFT JOIN product_attributes pa ON p.id = pa.product_id
LEFT JOIN attributes a ON pa.attribute_id = a.id
WHERE p.id = 12345;

CREATE INDEX idx_product_categories_product_id ON product_categories(product_id);
CREATE INDEX idx_product_categories_category_id ON product_categories(category_id);
CREATE INDEX idx_product_attributes_product_id ON product_attributes(product_id);
CREATE INDEX idx_product_attributes_attribute_id ON product_attributes(attribute_id);

2. Implementing Application-Level Caching

Fixing the query wasn’t enough. The product data, which was read-heavy but rarely changed, was a perfect candidate for caching. We implemented Redis, an in-memory data store, to cache the fully rendered product API responses.

// Pseudocode for getting a product
function getProduct($productId) {
    $cacheKey = "product:\$productId";
    $cachedProduct = $redis->get($cacheKey);
    
    if ($cachedProduct !== null) {
        return json_decode($cachedProduct, true); // Return cached data
    }
    
    // If not in cache, get from database (using the optimized query now!)
    $productData = queryDatabaseForProduct($productId);
    
    // Store in cache for 1 hour (3600 seconds)
    $redis->setex($cacheKey, 3600, json_encode($productData));
    
    return $productData;
}
?>

This simple code meant that after the first request, the next thousands of requests for the same product would be served from blazing-fast RAM, completely bypassing the database.

3. Database Connection Pooling

We identified that the application was opening a new database connection for each request, creating significant overhead. We implemented PgBouncer (for connection pooling) to efficiently manage and reuse database connections, reducing connection latency.

The Measurable Results

The impact was immediate and dramatic.