Slow query

[Gilles]

Hello,

Query Monitor is reporting the query below as a slow query with a time close or superior to 0.1 sec. Is there anything I can do to speed that up?

Thank you.

Slow Database Queries (above 0.05s)
Query Caller Time

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*, MIN(wp_postmeta.meta_value) as EventStartDate, MIN(tribe_event_end_date.meta_value) as EventEndDate
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.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 (48) )
AND ( wp_postmeta.meta_key = ‘_EventStartDate’ )
AND wp_posts.post_type = ‘tribe_events’
AND ((wp_posts.post_status = ‘publish’))
AND (wp_postmeta.meta_value >= ‘2017-04-24 01:25:22’
OR (wp_postmeta.meta_value <= ‘2017-04-24 01:25:22’
AND tribe_event_end_date.meta_value >= ‘2017-04-24 01:25:22’ ))
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order, EventStartDate ASC, wp_posts.post_date ASC
LIMIT 0, 10

W3TC\DbCache_Wpdb->default_query
+ 0.0957

[Barry]

Hi Gilles,

Great question!

There are a few options to mitigate the time queries like this one can take. It looks like you have W3TC installed which is great: does your host also offer object caching (memcache or redis)? If so, you can take advantage of this to great effect via W3TC.

Another option is enabling query caching at MySQL level and you may need to reach out to your host for assistance with that one, if it isn’t already enabled.

Would you be able to look into those?

[Gilles]

I have Redis object cache installed and activated through the Redis Object Cache plugin. My hosting service provides a Redis database for this purpose. Memcache is always running.

Looking at the query, it appears that it contains the current time. Therefore query caching is not really going to help much. If I could remove the seconds and maybe even round the minutes, that would help. But the query always works based on ‘now’. Any suggestions? does the Events Calendar plugin provide a rounding function? My events do not change second-by-second.

Thank you

[Gilles]

This is what I am calling:

// build and run query
$args = array( ‘category_name’ => ‘class’,
‘eventDisplay’ => ‘list’,
‘posts_per_page’ => 10,
‘post_status’ => ‘publish’,
);

$wp_query = tribe_get_events( $args, true );

[Barry]

OK, that’s a very fair point: for list/upcoming event queries it naturally bases the query on ‘now’ (otherwise there would potentially be some number of expired events showing in the upcoming events list).

If that’s something you can tolerate or that is unlikely to be a problem owing to the distribution of events within your site then you could potentially modify the query appropriately.

add_action( 'tribe_events_pre_get_posts', function( $event_query ) {
	// Only modify event list queries
	if ( 'list' !== $event_query->get( 'eventDisplay') ) {
		return;
	}

	// Modify start date to always use 00 seconds
	$start_date = $event_query->get( 'start_date' );
	$start_date = preg_replace( '/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:)(\d{2})$/', '${1}00', $start_date );
	$event_query->set( 'start_date', $start_date );
} );

The above is a possible start point, targeting only list view queries though it could potentially be widened and it reduces the accuracy to minute-level instead of to-the-second.

Would that help here, at all? You could of course tweak further to better fit your own data-set.

[Gilles]

Thank you for the code. It does help, even though the query is still flagged as ‘slow’ sometimes. Now I have figured out that most of time is spent in another places in the php code, so I am trying to look into that to improve page load times. Thank you for the help, I will close this issue for now.

[Barry]

Thanks, Gilles. Please do let us know by opening a new topic if we can help with anything else (or indeed if you have any other similarly useful feedback) 🙂

[Support Droid]

Hey there! This thread has been pretty quiet for the last three weeks, so we’re going to go ahead and close it to avoid confusion with other topics. If you’re still looking for help with this, please do open a new thread, reference this one and we’d be more than happy to continue the conversation over there.

Thanks so much!
The Events Calendar Support Team

[Author]

Posts