Improving performance on multisite environment — options?

[Mike Kirby]

We’ve experienced issues with slow queries on our multisite, this is affecting our database so can often slow down others sites too.

The query itself uses multiple left joins which seems to be the cause. Below is an example of the query we’ve seen issues with, it’s also listed as a “slow query” in query monitor and New Relic –

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_?_postmeta ON ( wp_?_posts.ID = wp_?_postmeta.post_id ) LEFT JOIN wp_?_postmeta AS mt? ON ( wp_?_posts.ID = mt?.post_id ) LEFT JOIN wp_?_postmeta AS mt? ON (wp_?_posts.ID = mt?.post_id AND mt?.meta_key = ? ) 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 = ? ) WHERE ?=? AND wp_?_posts.ID NOT IN (?) AND (
wp_?_postmeta.meta_key = ?
AND
(
( mt?.meta_key = ? AND CAST(mt?.meta_value AS DATETIME) < ? )
OR (
( mt?.meta_key = ? AND CAST(mt?.meta_value AS DATETIME) = ? )
AND mt?.post_id < ?
)
)
AND
mt?.post_id IS NULL
) AND wp_?_posts.post_type = ? AND (wp_?_posts.post_status = ?) GROUP BY wp_?_posts.ID ORDER BY EventStartDate DESC, wp_?_posts.ID DESC LIMIT ?, ?

Is there an alternative way to achieve this with less joins to improve performance?

[George]

Hey Mike,

Thank you for reaching out! I’m sorry to say it, but at this time our left joins are unfortunately the only way these features are pulled off in the plugin. Changing that is unfortunately a huge custom development task, so it’s not possible unless you yourself dive in and rewrite our plugins (or hire someone to do so).

We are constantly trying to improve the performance of our plugins and fully admit that, at present, performance is…sub-par in some cases. I’m sorry about these facts but just wanted to make it clear that we are trying to improve performance in upcoming releases.

One thing that might help at present, if you’re not using it already, is the “Month View Cache” option. You can find this in Events → Settings → Display in your wp-admin; when checked, this should help with performance a bit especially on the month view.

Sincerely,
George

[Support Droid]

This topic has not been active for quite some time and will now be closed.

If you still need assistance please simply open a new topic (linking to this one if necessary)
and one of the team will be only too happy to help.

[Author]

Posts