We’ve experienced issues with slow queries on our multisite, this is affecting our database so can often slow down others sites too.<\/p>\n
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 –<\/p>\n
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 (
\n wp_?_postmeta.meta_key = ?
\n AND
\n (
\n ( mt?.meta_key = ? AND CAST(mt?.meta_value AS DATETIME) < ? )
\n OR (
\n ( mt?.meta_key = ? AND CAST(mt?.meta_value AS DATETIME) = ? )
\n AND mt?.post_id < ?
\n )
\n )
\n AND
\n mt?.post_id IS NULL
\n) AND wp_?_posts.post_type = ? AND (wp_?_posts.post_status = ?) GROUP BY wp_?_posts.ID ORDER BY EventStartDate DESC, wp_?_posts.ID DESC LIMIT ?, ?<\/p>\n
Is there an alternative way to achieve this with less joins to improve performance?<\/p>\n","protected":false},"template":"","class_list":["post-1108757","topic","type-topic","status-closed","hentry"],"yoast_head":"\n