Recently, we encountered an issue while upgrading a clientâs WordPress site that utilizes The Events Calendar plugin from theeventscalendar.com. The upgrade caused a significant spike in database CPU usage, leading to severe performance degradation and eventual downtime. The site would become unresponsive within 1 to 12 hours post-deployment, forcing us to revert to the previous version.
Identifying the Issue: A Slow Query
After investigation, we isolated the problem to a specific SQL query that was being executed repeatedly:
SELECT SQL_CALC_FOUND_ROWS wp_4_posts.ID, CAST(wp_4_tec_occurrences.start_date_utc AS DATETIME) AS event_date_utc
FROM wp_4_posts
LEFT JOIN wp_4_term_relationships ON (wp_4_posts.ID = wp_4_term_relationships.object_id)
LEFT JOIN wp_4_term_relationships AS tt1 ON (wp_4_posts.ID = tt1.object_id)
LEFT JOIN wp_4_term_relationships AS tt2 ON (wp_4_posts.ID = tt2.object_id)
LEFT JOIN wp_4_term_relationships AS tt3 ON (wp_4_posts.ID = tt3.object_id)
LEFT JOIN wp_4_term_relationships AS tt4 ON (wp_4_posts.ID = tt4.object_id)
LEFT JOIN wp_4_term_relationships AS tt5 ON (wp_4_posts.ID = tt5.object_id)
LEFT JOIN wp_4_term_relationships AS tt6 ON (wp_4_posts.ID = tt6.object_id)
JOIN wp_4_tec_occurrences ON wp_4_posts.ID = wp_4_tec_occurrences.post_id
WHERE 1=1
AND (
(
wp_4_term_relationships.term_taxonomy_id IN (147,148,149,150,96)
AND tt1.term_taxonomy_id IN (147,148,149,150,96)
AND tt2.term_taxonomy_id IN (147,148,149,150,96)
AND tt3.term_taxonomy_id IN (147,148,149,150,96)
AND tt4.term_taxonomy_id IN (147,148,149,150,96)
AND tt5.term_taxonomy_id IN (147,148,149,150,96)
AND tt6.term_taxonomy_id IN (147,148,149,150,96)
)
)
AND CAST(wp_4_tec_occurrences.start_date_utc AS DATETIME) > '2024-06-24 03:59:59'
AND wp_4_posts.post_type = 'tribe_events'
AND wp_4_posts.post_status = 'publish'
GROUP BY wp_4_tec_occurrences.occurrence_id
ORDER BY wp_4_tec_occurrences.start_date_utc ASC, wp_4_posts.post_date ASC
LIMIT 0, 1;Each execution of this query was taking over 23 seconds, leading to a bottleneck that caused the CPU to max out at 100%. Clearly, this query needed optimization.
Investigating the Query: EXPLAIN Command to the Rescue
To better understand what was causing the delay, we turned to MySQLâs EXPLAIN command. By analyzing the query execution plan, we could pinpoint inefficiencies related to the joins and groupings, particularly involving the wp_4_term_relationships and wp_4_tec_occurrences tables.
We found that key indexes were either missing or not being utilized optimally. The absence of appropriate indexing on columns like object_id, term_taxonomy_id, and post_id meant that the database had to scan entire tables repeatedlyâleading to high CPU usage and slow response times.
The Fix: Adding Indexes
To resolve the issue, we added three specific indexes. These were designed to speed up the joins and comparisons that were bogging down the query:
CREATE INDEX idx_wp_4_posts_id ON wp_4_posts (ID);
CREATE INDEX idx_wp_4_term_relationships ON wp_4_term_relationships (object_id, term_taxonomy_id);
CREATE INDEX idx_wp_4_tec_occurrences ON wp_4_tec_occurrences (post_id, start_date_utc);By introducing these indexes, we were able to reduce the query execution time from 23 seconds down to just 1.3 secondsâa significant performance boost.
Monitoring the Results
To ensure everything was working smoothly in production, we continuously monitored our query performance using the following command:
SHOW FULL PROCESSLIST;This allowed us to track the running queries and verify that our optimization was successful. The CPU usage stabilized, and the site performance returned to normal without further issues.
Conclusion: Indexing Matters
If youâre running into similar performance issues after upgrading The Events Calendar plugin or any other database-intensive WordPress plugin, we recommend analyzing your queries using EXPLAIN and considering appropriate indexing. Note that your table names might differ depending on your WordPress setup, so adjust accordingly.
Optimizing database performance can be tricky, but with the right tools and approach, even complex queries can be tuned for efficiency.
