Slow SQL query
Hello! We have been experiencing performance problems with the Events Calendar plugin. Here is a specific database query that is taking 27 seconds on average to run on our (performant) Galera machines:
Reading mysql slow query log from /var/log/mysql/mariadb-slow.log.1
Count: 36 Time=27.38s (985s) Lock=0.00s (0s) Rows_sent=20.0 (720), Rows_examined=768183.3 (27654600), Rows_affected=0.0 (0), washingtonian_mysql[washingtonian_mysql]@[10.136.69.85]
SELECT DISTINCT wp_posts.*, IF (wp_posts.post_type = ‘S’, wp_postmeta.meta_value, wp_posts.post_date) AS EventStartDate FROM wp_posts LEFT JOIN wp_postmeta as wp_postmeta on wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = ‘S’ WHERE N=N AND wp_posts.post_type IN (‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’, ‘S’) AND ((wp_posts.post_status <> ‘S’ AND wp_posts.post_status <> ‘S’)) ORDER BY FIELD(wp_posts.post_type,’S’,’S’,’S’,’S’,’S’,’S’,’S’,’S’,’S’,’S’,’S’,’S’,’S’,’S’,’S’),wp_posts.menu_order ASC, wp_posts.post_title ASC
GROUP BY IF( post_parent = N, ID, post_parent )
ORDER BY EventStartDate ASC
LIMIT N, N
It looks like this has been a problem for a little while?
Is it possible to change the structure of this query to run more efficiently?
Interesting to see the number of other people reporting trouble with this 🙂
Thanks so much for reaching out again!
I’m sorry to hear that your site is running slowly with use of our plugins.
As a first step, please have a look at our Performance Considerations and see if there is anything you can adjust in order to help your site run better:
After that, you may be interested in caching, so take a look at this article: https://theeventscalendar.com/knowledgebase/caching-basics/
In addition, you can check out some of the solutions provided in this forum post:
I would also recommend looking into some of the plugins in this blog post, as they may be able to help you pinpoint where site slowness may be occurring:
In addition, you can try reverting <span style=”font-size: 0.875rem;”>back to version 4.4.18. </span>You should be able to download the earlier version from your account.
Let me know how it goes and if you need any further assistance on this topic!
My Name is Neill and as the PM here for support I just wanted to follow up and let you know that we are going to keep this thread open. We wanted to be clear here that there is indeed some room in our code for improvement, and as soon as we review this a little more with some dev and QA time we will work on a fix that may be included in an upcoming maintenance release.
Leaving this ticket opens means we can keep the conversation open if we have any more specific questions while troubleshooting, and allows us to notify you as soon as a fix becomes available.
To be clear, where you pinpointed the exact code, are you referring to the query in your first forum post, or have you opened a pull request or added a note via Github?
Thank you again for you initial report and for taking the extra steps here to help us dig deeper.
This thread hasnt seen any activity for quite some time and we are not accepting further replies.
If you need further help with this or any other issue, please create a new topic (you can of course include a link to this one if that helps to provide additional context) and one of the team will be only too happy to help.