What Went Wrong
Client's membership site had been growing steadily and hit 40,000 registered users. Over the past month, page load times had gradually increased from 2 seconds to over 12 seconds. The site frequently timed out entirely during peak hours (9 AM - 11 AM EST).
Their hosting provider had already upgraded them to a dedicated server ($200/month) but performance did not improve. The provider suggested migrating to a more powerful server for $450/month.
What We Found
Enabled MySQL slow query log and immediately identified the bottleneck: the membership plugin was running an unindexed JOIN query across the wp_users, wp_usermeta, and a custom membership_levels table on EVERY page load to check access permissions.
The query was scanning 2.4 million rows in wp_usermeta (40,000 users x ~60 meta keys each) with no relevant indexes. Each query took 4-8 seconds.
Additional findings:
- 847 expired transients consuming 120MB in the options table
- The autoload column in wp_options was loading 45MB of data on every request
- wp_postmeta had 1.2 million rows with no index on meta_key for the custom queries being run
- Object caching was not configured despite Redis being available on the server
How We Fixed It
1. Added composite indexes to wp_usermeta (user_id, meta_key) and the custom membership_levels table — query time dropped from 4-8 seconds to 12ms
2. Cleaned 847 expired transients and set up automated transient cleanup via WP-CLI cron
3. Audited wp_options autoload — set 340 rows to autoload=no, reducing autoload payload from 45MB to 3.2MB
4. Added missing index to wp_postmeta for the custom meta_key queries
5. Configured Redis object caching — installed Redis Object Cache plugin and tuned redis.conf for the workload
6. Implemented a persistent cache layer for membership checks: permission results cached per user for 15 minutes instead of querying on every page load
7. Set up MySQL performance monitoring dashboard via Percona PMM
Outcome
Page load times dropped from 12+ seconds to 0.8 seconds — a 93% improvement. Server CPU usage dropped from 95% to 15%. The client was able to DOWNGRADE their hosting plan from $200/month to $80/month since the optimized queries used a fraction of the resources.
The membership check cache alone eliminated 99.7% of the expensive JOIN queries. Six months later with 52,000 users, page loads remain under 1.2 seconds.