I have seen slow queries running on our site – I am not entirely sure where they are being called from yet, but wanted to pass this along for consideration:
SELECT wp_posts.ID
FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id )
WHERE 1=1 AND (
( wp_postmeta.meta_key = '_order' AND wp_postmeta.meta_value="xxxxxxxx" )
AND
( mt1.meta_key = '_user' AND mt1.meta_value="xxxxxx" )
) AND wp_posts.post_type="event_ticket" AND ((wp_posts.post_status="publish" OR wp_posts.post_status="pending"))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC;
This query is taking over 20s to run even though it returns zero rows. Removing the GROUP BY makes it return almost instantly. Perhaps you need to do this in pieces and verify the underlying data exists (order of the type for the user) before running it in such an expensive query?
If you have other suggestions or approaches to fix it, I would be glad to see anything implemented that would remove this slow query from our site. 🙂