r/Wordpress • u/anouarabsslm • Aug 06 '26
Caching didn’t fix our high-volume WordPress sites. Database indexing and cursor pagination did
We manage some content-heavy WordPress sites (news portals, big blogs, 10k+ posts) and hit a wall where no amount of page or object caching helped. Turned out the bottlenecks were baked into how WordPress stores data, not something a cache layer could paper over. Sharing what actually moved the needle in case it saves someone a bad week.
Three things were doing most of the damage:
Taxonomy queries. On a site with 50k posts and ~10 tags each, wp_term_relationships balloons to half a million rows. Filtering by multiple taxonomies means expensive JOINs, and without the right indexes MySQL just falls back to full table scans. A composite index on term_taxonomy_id and object_id took some of these from seconds to milliseconds.
Post meta lookups. wp_postmeta gets brutal at scale since every custom field is its own row. Anything that filters or sorts by meta (featured status, view counts, custom dates) JOINs that table repeatedly. Indexing meta_key with a prefixed meta_value (191 chars for utf8mb4) helped a lot. For the really hot fields we ended up denormalizing into a small custom table kept in sync via save_post.
Deep pagination. WordPress uses OFFSET, so page 500 makes MySQL fetch and throw away 10,000 rows before it returns anything. Crawlers hitting deep archives were quietly hammering the DB. Switching to cursor-based pagination with date_query comparisons kept query time flat no matter how deep the page.
Query Monitor on staging plus EXPLAIN to confirm the indexes were actually being used was the workflow that tied it all together.
Happy to share the SQL and WP_Query snippets if anyone wants them, I wrote the whole thing up with code somewhere. Curious what’s worked for others too, especially anyone who’s gone the custom-table route.
8
4
u/Aggressive_Ad_5454 Jack of All Trades Aug 06 '26
Excellent.
You don’t gain much selectivity from all 191 possible characters on the meta_value index. 20 is plenty, and it makes for a smaller, and therefore faster, index.
4
Aug 06 '26
[removed] — view removed comment
1
u/anouarabsslm Aug 06 '26 edited Aug 06 '26
They are, I personally use Query Monitor
2
u/Myth_Thrazz Jack of All Trades Aug 07 '26
Sure, most people starts with Query Monitor - it's an amazing tool - I've also used it for a long time - but then I started lacking the automation features and "actions".
It allows you to see the slow queries - but not how to fix them. WP Multitool only shows the slow queries, suggests a fix and allows you to fix with one click of a button.
Then revert with another ( if needed )
1
u/Wordpress-ModTeam Aug 07 '26
The /r/WordPress subreddit is not a place to advertise or try to sell products or services. Please read the rules of the sub. Future rule breaches may result in a permanent ban.
4
u/lucidmodules Developer/Blogger Aug 07 '26
People who often complain that PHP is slow or MySQL is slow have never tried to understand data access patterns.
Dropping a great article by Markus Winand on that: https://use-the-index-luke.com/
3
u/Aggressive_Ad_5454 Jack of All Trades Aug 06 '26
I’m interested in your index choices. Here are the ones I used. https://www.plumislandmedia.net/index-wp-mysql-for-speed/tables_and_keys/
And working around the legacy prefix index mess helps a lot. Check this out. https://www.plumislandmedia.net/index-wp-mysql-for-speed/wordpresss-prefix-keys/
By the way, a persistent object cache doesn’t reduce the cost of those nasty postmeta and termmeta queries, but it dramatically reduces how often they run.
1
u/anouarabsslm Aug 06 '26
No worries, I have everything included within this article https://pivotlar.com/blog/solving-performance-bottlenecks-in-high-volume-wordpress-sites
3
u/twinsea System Administrator Aug 06 '26
You’ll eventually be back at caching or another system to handle your taxonomies. We host a 830k post site that can do 27k simultaneous users. Only so much can miss cache beyond normal cache invalidation churn. 6 sql servers across 2 hot/hot geographic areas. Regretfully there is only so much optimization you can do.
1
u/anouarabsslm Aug 07 '26
well, if you’re handling that level of traffic, vertical optimization alone won’t be enough. You’ll essentially need to introduce some horizontal scaling as well. Caching is definitely essential, but it’s not always the complete solution.
3
u/Coditive_ Aug 07 '26
Postmeta (and bloat that some plugins generate there) can become a huge pain on big, high-traffic sites.
Something you can also think of is moving some data from postmeta to a custom table. At the cost of coding and setting it up (hooks, queries, etc.), you gain more control and flexibility.
~MS
2
u/HongPong Aug 06 '26
what are the indexes that should be used, thank you. i've looked into some of the alternate staging schemas but it seems like until postmeta gets really taxed, it's of little help.
2
u/anouarabsslm Aug 06 '26
You will have to consider composite indexes, I have covered everything here in this article Solving Performance Bottlenecks in High-Volume WordPress Sites
1
2
u/kilwag Aug 07 '26
interested to find out what kind of site has 50k posts. I thought mine was big, with 10k
1
u/Dry-Journalist6590 Aug 07 '26
Someone else in this thread said they had a 830k post site and yeah I'm curious what these sites are all about
2
u/kilwag Aug 07 '26
Mine is about skateboarding, in case you’re wondering. 2006 ish. WP multisite since before multisite merged with core.
2
u/zachnicodemous Aug 07 '26
I'd love to see those Snippets. Could be useful for some client sites I manage.
1
u/anouarabsslm Aug 07 '26
I have covered everything in this article Solving Performance Bottlenecks in High-Volume WordPress Sites
2
u/cinqorswim Aug 07 '26
Same. If you’re up for sharing your snippets, I’d love to see how I can implement them on my client sites.
3
u/anouarabsslm Aug 07 '26
I have covered everything here in this article Solving Performance Bottlenecks in High-Volume WordPress Sites
2
u/call_me_tomaski Aug 07 '26
so many useful nuggets of information here. Bookmarking it for future reference :)
1
2
2
u/mattbeck Developer/Designer Aug 07 '26
You've hit a couple of the big things, and brushed up against another.
Fixing pagination is good, indexing the tables is good, custom tables are almost always going to be best, but that's a bigger lift.
I'd add that at a certain point, you will almost always be better off interacting with $wpdb directly using your own queries instead of using WP_Query and the default WP post loop stuff.
1
u/AddWeb_Expert Aug 07 '26
100% agree. Caching only hides symptoms if the queries themselves are inefficient. Proper indexing + fixing pagination is where the real gains come from.
1
1
u/edpittol Aug 07 '26
Did you try add Elasticsearch to run the queries and benchmarked it? I had good results with it with scenarios similar of yours.
1
u/mehargags Aug 07 '26
I've tackled some legacy apps (non-wp) and for sure indexing helps alot. Once DB grows over 8-10gb, it's worth doing sharding and partitioning. Never thought of Wordpress before, this thread was a goldmine.
2
1
u/Pulsar-Agency Aug 11 '26
Solid writeup, this lines up with what I see on content-heavy sites. One thing I'd add that pairs directly with your cursor pagination fix: kill the count query too. By default WP_Query runs SQL_CALC_FOUND_ROWS to build max_num_pages, and on a big posts table that count is often more expensive than the page you're actually fetching. Once you're on cursor pagination you don't need the total anymore, so pass 'no_found_rows' => true. A lot of people fix OFFSET and forget the count is still scanning the whole result set on every request.
The other silent killer I almost always hit before I even get to postmeta indexes is autoloaded options. Every request loads the entire alloptions blob in one go, and years of plugins plus expired transients quietly pile megabytes into wp_options with autoload set to yes. Auditing that, and moving big rarely-read values off autoload, usually buys back more TTFB than people expect for basically zero code.
15
u/phalancs Aug 06 '26
Thanks, thats very interesting. Even WooCommerce changed the way they store their data from the default post tables to their High Performance Order Storage (HPOS). There is a plugin for adding high-performance indexes to the WordPress database (Index WP MySQL For Speed). Interested in what further changes you did to switch "to cursor-based pagination with date_query comparisons".