MySQL at Scale with Laravel: Notes From Real Applications

I covered N+1 queries in another post. This one is about MySQL performance more broadly — specifically the stuff I've dealt with on real applications that were actually under load, not toy projects.

First: EXPLAIN Every Slow Query

Before you optimize anything, run EXPLAIN on it. MySQL will tell you exactly what it's doing:

EXPLAIN SELECT posts.*, users.name as author_name
FROM posts
JOIN users ON posts.user_id = users.id
WHERE posts.is_published = 1
ORDER BY posts.published_at DESC
LIMIT 20;

You're looking at the type column:

• ALL — full table scan. Almost always bad on large tables.
• index — full index scan. Better than ALL but still scans everything.
• ref or eq_ref — using an index to find rows. This is what you want.
• const — single row lookup. Ideal.

If you see Extra: Using filesort, your ORDER BY isn't covered by an index and MySQL is sorting results in memory. On large tables this kills performance.

Index Design That Actually Helps

The order of columns in a composite index matters. MySQL can use a composite index for WHERE columns in prefix order — but only until it hits a range condition:

// This query:
// WHERE user_id = 5 AND is_published = 1 ORDER BY published_at DESC

// Benefits from this composite index (equality columns first, range/order last):
$table->index(['user_id', 'is_published', 'published_at']);

If you put published_at first, the index won't be used efficiently for user_id filtering. Get the order right.

Enable the Slow Query Log in Production

I always enable this. You can't fix what you can't see:

-- Add to MySQL config (my.cnf)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

In Laravel, supplement this with application-level logging:

// AppServiceProvider
DB::whenQueryingForLongerThan(1000, function () {
    Log::warning('Slow query', [
        'url'    => request()?->fullUrl(),
        'queries' => DB::getQueryLog(),
    ]);
});

Cursor Pagination at Scale

Offset pagination (LIMIT x OFFSET y) gets progressively slower as the offset increases — MySQL has to scan and discard all the skipped rows. At page 500 with 20 per page, that's 10,000 rows scanned to return 20.

Cursor pagination avoids this entirely by using WHERE id > last_seen_id:

// Slow at large offsets — LIMIT 10000, 20
Post::paginate(20);

// Constant performance regardless of page depth
Post::cursorPaginate(20);

The tradeoff: cursor pagination doesn't support jumping to arbitrary pages. For most feeds and timelines, that's fine.

Read Replicas

For read-heavy applications, separate your reads from your writes. Laravel's database config handles this natively:

'mysql' => [
    'read' => [
        'host' => ['replica-1.db', 'replica-2.db'],
    ],
    'write' => [
        'host' => 'primary.db',
    ],
    'sticky' => true, // Use write connection for reads in same request
],

sticky: true means if you just wrote a record, reads in the same request go to the primary too. Without this, you'd read your own writes before replication catches up — a subtle bug that's annoying to diagnose.

Cache the Right Things

Not everything needs to be cached, but expensive rarely-changing queries definitely do. I use cache tags for related invalidation:

function getPopularCategories(): Collection
{
    return Cache::tags(['categories'])
                ->remember('popular-categories', 3600, function () {
                    return Category::withCount('publishedPosts')
                                   ->orderByDesc('published_posts_count')
                                   ->limit(10)
                                   ->get();
                });
}

// When a category is updated
Cache::tags(['categories'])->flush();

The Real Performance Wins

In my experience the order of impact is roughly: fix N+1 queries first (biggest win), then add missing indexes, then add caching on expensive queries, then consider read replicas when you're actually bottlenecking on read throughput. Most apps never get past step two.