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.

What Are “High-Performance Keys”?

“High-performance keys” are just smarter indexes — they help MySQL find what WordPress is asking for without rummaging through half a million rows first.

In MySQL, a key is just another name for an index — a data structure that lets the database find rows faster without scanning the entire table.

A high-performance key (as the plugin creates) is an index that’s carefully designed to match the way WordPress actually queries its data. It combines multiple columns that are frequently used together in WHERE and ORDER BY clauses, so MySQL can skip sorting and full-table scans.

For example:

CREATE INDEX type_status_date ON wp_posts (post_type, post_status, post_date, ID);

That one index can satisfy dozens of common WordPress queries — posts by type, by status, ordered by date — all in a single efficient lookup.

By contrast, WordPress’s default indexes are very generic (for example, just post_date or just post_name). Those work fine for small sites, but once you have hundreds of thousands of posts or attachments, they’re too narrow. MySQL ends up sorting and scanning huge chunks of data anyway.

Why the Plugin’s Keys Are “High-Performance”

The Index WP MySQL For Speed plugin does three smart things:

  1. Combines columns that WordPress frequently filters by (post_type, post_status, post_date, ID, etc.).
  2. Replaces single-column indexes with compound ones that can satisfy more queries per index.
  3. Adds indexes for meta lookups (post_id, meta_key, meta_value) so joins on wp_postmeta are faster.

The result? Fewer full scans, fewer temporary filesorts, and dramatically faster query times for things like the Media Library, admin lists, and search results — especially at 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.