Performance with over 600 events

[Jason]

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, 9 months ago by  Jason. Reason: forgot to add the query code

[Stephen Harris]

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.

[Jason]

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.

[Author]

Posts