This Query takes longer than 10 minutes

[Sonny Parlin]

After setting up a MySQL slow log to find out why our database has been recently getting overloaded, the log showed this query taking longer than 10 minutes, and the log is full of them. Can someone explain to me what exactly this query is doing and what part of the plugin executes it?

SELECT Max(lat) max_lat,
Max(lng) max_lng,
Min(lat) min_lat,
Min(lng) min_lng
FROM (SELECT post_id AS venue_id,
CASE
WHEN meta_key = ‘_VenueLat’ THEN meta_value
end AS LAT,
CASE
WHEN meta_key = ‘_VenueLng’ THEN meta_value
end AS LNG
FROM wp_postmeta
WHERE ( meta_key = ‘_VenueLat’
OR meta_key = ‘_VenueLng’)
AND post_id IN (SELECT meta_value FROM wp_postmeta WHERE meta_key=’_EventVenueID’)
) coors;

[Barry]

Hi!

We are aware that query can be a fairly intensive one, and so its result is actually cached so that it doesn’t need to run on every single page load. It’s role is essentially to calculate the geographic centre of all events that have geolocation data attached to them (so that the map in map view can be appropriately centred, etc).

I would note that we may anyway be revising this as it currently pulls in events that have since passed or are not published – and that may be part of the reason you are hitting this issue.

We’d certainly be interested to know how many events you have that might cause such a long delay, though – can you provide even an approximate figure?

[Sonny Parlin]

Here is are the numbers:

View post on imgur.com

I’m not sure why we have so many drafts, but these are the numbers. Maybe have that query exclude drafts?

[Barry]

Thanks for sharing those details: yes, I can envision there being problems under those circumstances – and we do have an issue on the tracker to address just this point. Ordinarily we don’t recommend tampering with core plugin code but in this case, if you feel comfortable doing so, you might be able to get some relief here by editing the following file:

events-calendar-pro/lib/tribe-geoloc.class.php

The method we’re interested in is TribeEventsGeoLoc::estimate_center_point() which lives in lines 723-764. Within that if you look for the following line:

set_transient( self::ESTIMATION_CACHE_KEY, $data, 5000 );

This sets the number of seconds that the geographic centre is cached for. Changing 5000 to something like 604800 (one week) or 2419200 (one month) would result in this running less frequently – and if your events generally take place in the same area then that is probably a valid way to go.

set_transient( self::ESTIMATION_CACHE_KEY, $data, 604800 );

The major downside to editing core code is of course having to restore changes after updates, but though I can’t offer up any guarantees I’d like to think we will have a better solution (at least so you can set up a filter to achieve the same thing) available in one of our next releases.

Thanks again – and apologies for the inconvenience in the interim.

[Sonny Parlin]

I’ve made the changes, I guess it’s wait and see time. Thanks for your help! I’m going to keep the issue open so I can post updates.

[Sonny Parlin]

This appears to have resolved the issue, or at least gave us some relief. Thanks!

[Barry]

Hi! We’ve now completed some work that ought to further address this issue in our very next upcoming release. Thanks for your patience and if you do hit any further problems with this once you’ve updated please don’t hesitate to let us know in a new thread – thanks!

[Author]

Posts