Performance with over 600 events

This topic contains 2 replies, has 2 voices, and was last updated by  Jason 2 years, 12 months ago.

Viewing 3 posts - 1 through 3 (of 3 total)
  • Author
    Posts
  • #39549

    Things were going great with your plugin, we customized it a bit so that venues can have a taxonomy and added some Advanced Custom Fields to events and venues and everything was working the way we wanted it. Until a few weeks ago everything seemingly started to slow down. It seemed like once we were over 600 events. I installed Query Monitor when it takes 45 seconds to load the Events list in WordPress, the only Slow Query is the one that queries FROM wp_posts LEFT JOIN wp_eo_events. Loading any other admin page including individual events are much quicker at 4 or 5 seconds.

    To make sure it wasn’t our hosting (which is a decent VPS through A2), I copied the whole site to another hosting package and loaded it up on a subdomain. Same issue and similar timing. So I stripped down the test site removing all unnecessary plugins, removed ACF, removed our taxonomy and custom shortcodes, and measured some more. Then Events list in the admin sped up but it’s still taking 15 seconds or more. And when I load the Events page on the front end, it’s take over a minute to load. If I go to any other page that doesn’t load any events, it loads up quick.

    Admin query of page 2 of the Events list that took 20s:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.*, wp_eo_events.event_id, wp_eo_events.event_id AS occurrence_id, wp_eo_events.StartDate, wp_eo_events.StartTime, wp_eo_events.EndDate, wp_eo_events.FinishTime, wp_eo_events.event_occurrence
    FROM wp_posts
    LEFT JOIN wp_eo_events
    ON wp_posts.ID = wp_eo_events.post_id
    WHERE 1=1
    AND wp_posts.post_type = 'event'
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'confirmed'
    OR wp_posts.post_status = 'future'
    OR wp_posts.post_status = 'draft'
    OR wp_posts.post_status = 'pending'
    OR wp_posts.post_status = 'private')
    GROUP BY wp_eo_events.post_id
    ORDER BY wp_eo_events.StartDate ASC, wp_eo_events.StartTime ASC
    LIMIT 20, 20
    

    And the query of the [event_search] list on the front end that took 67s:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.*, wp_eo_events.event_id, wp_eo_events.event_id AS occurrence_id, wp_eo_events.StartDate, wp_eo_events.StartTime, wp_eo_events.EndDate, wp_eo_events.FinishTime, wp_eo_events.event_occurrence
    FROM wp_posts
    LEFT JOIN wp_term_relationships
    ON (wp_posts.ID = wp_term_relationships.object_id)
    LEFT JOIN wp_eo_events
    ON wp_posts.ID = wp_eo_events.post_id
    LEFT JOIN (SELECT post_id, StartDate, StartTime
    FROM wp_eo_events
    WHERE (wp_eo_events.EndDate > '2021-03-28'
    OR (wp_eo_events.EndDate = '2021-03-28'
    AND wp_eo_events.FinishTime > '01:05:45')) ) AS wp_eo_events2
    ON wp_eo_events.post_id = wp_eo_events2.post_id
    AND TIMESTAMP(wp_eo_events.StartDate,wp_eo_events.StartTime) > TIMESTAMP(wp_eo_events2.StartDate,wp_eo_events2.StartTime)
    WHERE 1=1
    AND ( wp_term_relationships.term_taxonomy_id IN (244) )
    AND wp_posts.post_type = 'event'
    AND (wp_posts.post_status = 'publish'
    OR wp_posts.post_status = 'confirmed'
    OR wp_posts.post_status = 'private')
    AND (wp_eo_events.EndDate > '2021-03-28'
    OR (wp_eo_events.EndDate = '2021-03-28'
    AND wp_eo_events.FinishTime > '01:05:45'))
    AND wp_eo_events2.StartDate is NULL
    GROUP BY wp_eo_events.event_id
    ORDER BY wp_eo_events.StartDate ASC, wp_eo_events.StartTime ASC
    LIMIT 0, 10
    

    When I load up the database in phpMyAdmin and run these queries, it takes the same amount of time to run as on the website. So it’s definitely the query and/or the data that is causing the issue and nothing else in WordPress.

    I’m assuming this plugin is supposed to be able to handle over 600 events… I’ve seen other comments about slowness with 6000+ events, how were those solved?

    Happy to provided you with access to our test site to investigate.

    Thanks.

    • This topic was modified 3 years ago by  Jason. Reason: forgot to add the query code
    Jason
    #39574

    Hi Jason,

    Are you ‘grouping occurrence’ in the plug-in settings? Or otherwise in the shortcode settings (the second query suggests you might be). Unfortunately due to the limitations of the indexes on wp_post table there is a significant performance hit when ‘grouping occurrences’, essentially because you have to query the relevant dates in eo_events before joining it to wp_post.

    In the first instance I’d recommend disabling that, and seeing if that provides any improvements, at least in the front-end.

    Stephen Harris
    #39582

    Hi Stephen,

    That was totally it. Night and day difference by turning off ‘grouping occurrence’. Queries that we taking 40 seconds all of a sudden took under a second.

    It’s a shame, we really need that feature… We show many daily events in list form and it completely saturates the list so that you can’t easily see other events that are coming up.

    In your experience, is there a recommended approach for this needed behaviour? Or is there anything that can be done manually to assist with indexing properly?

    Thanks for any help.

    Jason
Viewing 3 posts - 1 through 3 (of 3 total)
To enable me to focus on Pro customers, only users who have a valid license for the Pro add-on may post new topics or replies in this forum. If you have a valid license, please log-in or register an account using the e-mail address you purchased the license with. If you don't you can purchase one here. Or there's always the WordPress repository forum.