Multiple SQL searches for the same thing

[eclair]

Hi, I have TEC Pro 3.8.1. I have a site that I’m developing for a client, and we’re looking at about 22k+ event entries (they have about 200 events that recur weekly). So I understand performance will suffer a bit with the number of entries. That much I get, and I’ll work on optimizing and reducing the number of entries.

But what I noticed is TEC makes multiple searches for the same or similar things. In particular, I saw the following SQL queries (3 of them each, taking 8-10 seconds to complete) for just a default list view:

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, MIN(wp_postmeta.meta_value) as EventStartDate, tribe_event_end_date.meta_value as EventEndDate FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) LEFT JOIN wp_postmeta as tribe_event_end_date ON ( wp_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) AND tt1.term_taxonomy_id IN (5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) ) AND wp_posts.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘pending_payment’ OR wp_posts.post_status = ‘pending_off_payment’ OR wp_posts.post_status = ‘private’) AND (wp_postmeta.meta_key = ‘_EventStartDate’ ) AND (wp_postmeta.meta_value >= ‘2014-11-25 00:00:00’ OR (wp_postmeta.meta_value <= ‘2014-11-25 00:00:00’ AND tribe_event_end_date.meta_value >= ‘2014-11-25 00:00:00’ )) GROUP BY wp_posts.ID ORDER BY DATE(MIN(wp_postmeta.meta_value)) ASC, TIME(wp_postmeta.meta_value) ASC LIMIT 10, 10

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, MIN(wp_postmeta.meta_value) as EventStartDate, tribe_event_end_date.meta_value as EventEndDate FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) LEFT JOIN wp_postmeta as tribe_event_end_date ON ( wp_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) AND tt1.term_taxonomy_id IN (5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) ) AND wp_posts.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘pending_payment’ OR wp_posts.post_status = ‘pending_off_payment’ OR wp_posts.post_status = ‘private’) AND (wp_postmeta.meta_key = ‘_EventStartDate’ ) AND (wp_postmeta.meta_value >= ‘2014-11-25 00:00:00’ OR (wp_postmeta.meta_value <= ‘2014-11-25 00:00:00’ AND tribe_event_end_date.meta_value >= ‘2014-11-25 00:00:00’ )) GROUP BY wp_posts.ID ORDER BY DATE(MIN(wp_postmeta.meta_value)) ASC, TIME(wp_postmeta.meta_value) ASC LIMIT 0, 10

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, MIN(wp_postmeta.meta_value) as EventStartDate, tribe_event_end_date.meta_value as EventEndDate FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_relationships AS tt1 ON (wp_posts.ID = tt1.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) LEFT JOIN wp_postmeta as tribe_event_end_date ON ( wp_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = ‘_EventEndDate’ ) WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) AND tt1.term_taxonomy_id IN (5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) ) AND wp_posts.post_type = ‘tribe_events’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘pending_payment’ OR wp_posts.post_status = ‘pending_off_payment’ OR wp_posts.post_status = ‘private’) AND (wp_postmeta.meta_key = ‘_EventStartDate’ ) AND (wp_postmeta.meta_value >= ‘2014-11-25 00:00:00’ OR (wp_postmeta.meta_value <= ‘2014-11-25 00:00:00’ AND tribe_event_end_date.meta_value >= ‘2014-11-25 00:00:00’ )) GROUP BY wp_posts.ID ORDER BY DATE(MIN(wp_postmeta.meta_value)) ASC, TIME(wp_postmeta.meta_value) ASC LIMIT 10, 10

All 3 queries are identical as far as I can see, with the exception of the middle one having a different LIMIT range.

I have included the full Debug Bar output here: http://pastebin.com/25MnfDyH

Any ideas why TEC is making multiple queries for the same thing? Thank you.

[Barry]

Hi eclair,

I’m sorry you’ve hit difficulties with calendar performance – but thanks for taking the time to post and provide the level of detail that you did.

Though I need to do a little more research it looks as if the last two “big” queries you referenced run in relation to our pagination template tags and it does seem like that is something we could potentially do in a more efficient manner.

If you can bear with us a little longer we’ll check into this and see if there are indeed some opportunities to increase efficiency here 🙂

[Barry]

@eclair,

I wonder if you would be able to test something out.

If you already have custom template overrides in place this may need a little more explanation, but essentially I wonder if you could take this code and save it to a new file located in your theme as follows:

wp-content/themes/YOUR_THEME/tribe-events/list/nav.php

Of course, modify that path appropriately to match that of your own theme.

In some cases (such as if you have navigated beyond the first page of results in list view) it should remove the need for some of those extra queries you noted altogether. In other cases some extra queries still may need to run but – hopefully – they will be of a notably less intensive nature.

 

[eclair]

Thanks Barry, I’ll give it a test and I’ll let you know what happens. We’re hitting Thanksgiving, so it might be a couple of days. Thanks for your prompt response!

[eclair]

Looks good so far… Much faster, and I don’t see the multiple queries for the same thing. I will test it out a bit further and play around with it and let you know!

[Barry]

Excellent, glad to hear it’s looking like it made a positive difference 🙂

What I’ll do is leave this open a while longer in case there’s any other relevant feedback/problems etc – but looking to the future we’ll have a think about baking this sort of approach into the plugin itself (and indeed will examine possibilities for further efficiency gains in this area) – so hopefully the workaround won’t be needed indefinitely.

Thanks again!

[eclair]

Thanks Barry… So far the change is working well… Functionality seems to be fine as well (doesn’t look like anything broke in the process). Thanks very much for this fix. Definitely try to incorporate this change in your next release! 🙂

[Barry]

Awesome, it’s great to hear that it has been effective and though the change probably won’t make the cut for our very next release we’ll do our best to look at integrating it as soon after that as we can.

Thanks again!

[Author]

Posts