It started with what looked like a simple problem. My client tried to upload an image to his site, only to be greeted with:

The server cannot process the image. This can happen if the server is busy or does not have enough resources to complete the task.

Strange. The image was only 2000×2029px, well under WordPress’s suggested maximum of 2560px. This should have been easy. But when I looked at the logs, things got ugly.

The 504 Storm

The Nginx error log was full of:

upstream timed out (110: Connection timed out) while reading response header from upstream

Translation: PHP-FPM was choking. And not just on uploads — soon even the Media Library itself was throwing 504 Gateway Timeouts.

That’s when I realized: this wasn’t about one unlucky JPEG. This was about the entire Media Library.

How Big Is Too Big?

So I cracked open WP-CLI:

wp post list --post_type=attachment --format=count

Result: 483,000+ attachments.

And when I broke it down by type:

wp post list --post_type=attachment --field=post_mime_type | sort | uniq -c | sort -nr

I discovered that 482,938 were JPEGs.
That’s almost half a million images. Suddenly, the 504s made sense.

Why WordPress Falls Over

By default, the Media Library runs a query like this:

SELECT SQL_CALC_FOUND_ROWS * 
FROM wp_posts 
WHERE post_type = 'attachment' 
ORDER BY post_date DESC 
LIMIT 0,40;

With half a million attachments, SQL_CALC_FOUND_ROWS becomes deadly. MySQL has to count every single matching row before serving the first 40 results. Add in metadata joins from wp_postmeta and WordPress was guaranteed to hit timeouts.

The Fix: Indexes to the Rescue

Step one: I measured the query performance with:

wp db query "EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM wp_posts WHERE post_type='attachment' ORDER BY post_date DESC LIMIT 0,40;"

The output confirmed WordPress was scanning ~399k rows and doing a filesort. Ouch.

The real breakthrough came from installing the excellent Index WP MySQL For Speed plugin. It rewrote the database indexes with high-performance keys, optimized for exactly this kind of WordPress scale.

Testing After Indexing

From the CLI, I re-ran:

time wp db query "SELECT SQL_NO_CACHE * FROM wp_posts WHERE post_type='attachment' ORDER BY post_date DESC LIMIT 40;" > /dev/null

What once took seconds dropped to milliseconds.

And a real WP-style test:

wp eval '
$query = new WP_Query([
  "post_type" => "attachment",
  "posts_per_page" => 40,
  "orderby" => "date",
  "order" => "DESC",
]);
echo "Found {$query->found_posts} attachments\n";
'

It still reported nearly half a million attachments, but it did so fast. That’s the magic of proper indexing.

Lessons Learned

  1. Size matters — WordPress can technically store hundreds of thousands of attachments, but don’t expect the default Media Library to be happy about it.
  2. Indexes matter even more — a couple of carefully chosen composite indexes can be the difference between a 504 timeout and a snappy response.
  3. SQL_CALC_FOUND_ROWS is evil — disabling it in favor of smarter pagination is another key performance win.
  4. Think long-term — with libraries this big, offloading media (to S3/Spaces/Cloudflare R2) is often the only sustainable option.

Takeaway

If your Media Library is big enough to make WordPress groan, don’t panic.

  • Start with diagnostics (wp post list, EXPLAIN, time wp db query).
  • Add proper indexes (the plugin I used makes it painless).
  • And accept that at scale, you need to treat WordPress like a database-backed app, not just a blogging engine.

For me, the journey started with one stubborn JPEG — and ended with a crash course in WordPress at scale.