MySQL Optimization: How We Handled 100,000 Daily Queries on PHP 5.3

In early 2011 we were running a regional news portal on a shared hosting stack: Apache 2.2, PHP 5.3, MySQL 5.1 with MyISAM tables. Traffic grew faster than expected and the database became the bottleneck. Page generation times climbed to 4-8 seconds. Here's what we did.

---

The Problem with MyISAM at Scale

MyISAM uses table-level locking. Every INSERT or UPDATE locks the entire table - all concurrent SELECT queries wait. With 60-70 writes per minute (comments, pageview counters, analytics inserts) and hundreds of concurrent readers, contention was constant.

Profiling with SHOW PROCESSLIST revealed a queue of 15-30 threads waiting on Locked status during peak hours.

-- The query causing most pain: updating pageview counter
UPDATE articles SET views = views + 1 WHERE id = ?;

-- With MyISAM: this locks the entire articles table
-- Every article page load triggered this - pure poison for a news site

---

Migration to InnoDB

InnoDB uses row-level locking. The same UPDATE above only locks the specific row, not the entire table. For a read-heavy site with frequent counter updates, this is the fundamental fix.

Migration steps:

-- Check current engine
SELECT TABLE_NAME, ENGINE 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_db';

-- Convert table (zero downtime for reads during conversion on small tables)
ALTER TABLE articles ENGINE=InnoDB;

-- For large tables: create new table, copy data, rename
CREATE TABLE articles_new LIKE articles;
ALTER TABLE articles_new ENGINE=InnoDB;
INSERT INTO articles_new SELECT * FROM articles;
RENAME TABLE articles TO articles_old, articles_new TO articles;

Immediate results: Average query wait time dropped from 340ms to 45ms on write-heavy tables. Page generation times fell to 1.2-1.8 seconds.

---

Adding Memcached for Expensive Queries

Some queries were inherently slow regardless of engine - complex JOINs across 4-5 tables for homepage aggregates, category counts, tag clouds.

<?php
// PHP 5.3 Memcached pattern
$memcache = new Memcache();
$memcache->connect('127.0.0.1', 11211);

function getCategoryArticles($categoryId, $limit = 20) {
    global $memcache, $db;
    
    $cacheKey = "cat_articles_{$categoryId}_{$limit}";
    $cached = $memcache->get($cacheKey);
    
    if ($cached !== false) {
        return $cached;
    }
    
    // The expensive query
    $result = $db->query("
        SELECT a.id, a.title, a.created_at, a.views,
               u.username, c.name as category_name,
               COUNT(cm.id) as comment_count
        FROM articles a
        JOIN users u ON a.author_id = u.id
        JOIN categories c ON a.category_id = c.id
        LEFT JOIN comments cm ON a.id = cm.article_id
        WHERE a.category_id = {$categoryId}
          AND a.published = 1
        GROUP BY a.id
        ORDER BY a.created_at DESC
        LIMIT {$limit}
    ");
    
    $data = $result->fetchAll(PDO::FETCH_ASSOC);
    
    // Cache for 5 minutes
    $memcache->set($cacheKey, $data, 0, 300);
    
    return $data;
}

Cache invalidation - the hard part:

// After publishing a new article, clear affected caches
function invalidateCategoryCache($categoryId) {
    global $memcache;
    // Clear all limit variants
    foreach ([10, 20, 50] as $limit) {
        $memcache->delete("cat_articles_{$categoryId}_{$limit}");
    }
    $memcache->delete("homepage_latest");
    $memcache->delete("category_counts");
}

---

Index Audit

After fixing the engine and adding caching, we ran EXPLAIN on every slow query in the log:

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries > 1 second

-- Then analyze
EXPLAIN SELECT * FROM articles 
WHERE category_id = 5 AND published = 1 
ORDER BY created_at DESC;

-- Result showed full table scan: type = ALL
-- Fix:
CREATE INDEX idx_articles_category_published 
ON articles(category_id, published, created_at DESC);

-- Re-run EXPLAIN: type = range, rows examined dropped from 45,000 to 23

---

Results After All Three Changes

Metric	Before	After
Average page generation	4.2 sec	0.4 sec
DB queries per page	18-24	3-5 (rest cached)
Peak concurrent connections	180-220	40-60
Server load average	8-12	1.2-2.1
Monthly hosting bill	$180 (overages)	$60 (base plan)

The server went from struggling to having headroom. We scaled to 300,000 daily pageviews on the same hardware.

---

What We'd Do Differently Today

This was 2011. In 2024, the same problem gets solved with:

• Redis instead of Memcached (better data structures, persistence, pub/sub)
• Read replicas for horizontal read scaling
• Query caching at ORM level (Prisma, Eloquent)
• Connection pooling (PgBouncer equivalent for MySQL: ProxySQL)

But the fundamentals haven't changed: identify the bottleneck, fix locking, cache expensive reads, index your queries. The tools evolve; the principles don't.

---

Aunimeda builds backend systems with optimized database architectures - PostgreSQL, Redis, ClickHouse, and more.

Contact us for backend and database engineering. See also: Custom Software Development