Help Desk

Slow SQL query

  • Posts: 11 Topics: 10
    | Permalink

    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
    SQL_CALC_FOUND_ROWS *
    FROM (
    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
    ) a
    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?
    https://theeventscalendar.com/support/forums/topic/slow-page-load-time-due-to-sql-query/

    Is it possible to change the structure of this query to run more efficiently?

    Thank you,
    Chris

    Posts: 11 Topics: 10
    | Permalink

    Interesting to see the number of other people reporting trouble with this 🙂

    https://theeventscalendar.com/?s=SQL_CALC_FOUND_ROWS&submit=Search

    Posts: 5926
    | Permalink

    Hi Chris,

    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:

    https://theeventscalendar.com/knowledgebase/performance-considerations/

    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:

    https://theeventscalendar.com/support/forums/topic/decreasing-loading-time-for-big-amount-of-events/

    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:

    https://theeventscalendar.com/favorite-developer-plugins/

    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!

    Thanks,

    Jaime

     

    Posts: 11 Topics: 10
    | Permalink

    Thank you! We have pinpointed the exact query where the slowdown is occurring and are suggesting a fix in the code, as others have done 🙂

    Thank you,
    Chris

    Posts: 5926
    | Permalink

    Hi Chris,

    Glad we could help, you are welcome back in our support forums any time!

    For now, I am going to close this thread.

    Have a great week!

    | Permalink
    Posts: 381 Answers: 37
    | Permalink

    Hi Chris,

    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.

    Neill

This thread hasn’t 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.