I recently had a quite simple meta_query produce really slow response times – 9 seconds to return 2 rows! The meta_query was simple:

[meta_query= array(array(key => 'mykey'
                            [value] => array(3956)
                            [compare] => IN
                        )
)

Resulted in a query such as

SELECT SQL_CALC_FOUND_ROWS 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_posts.post_type = 'myposttype'
AND ((wp_posts.post_status = 'publish'))
AND ( wp_postmeta.meta_key = 'anotherkey'
AND ( ( mt1.meta_key = 'mykey' AND CAST(mt1.meta_value AS CHAR) IN ('3956')))
)
GROUP BY wp_posts.ID… Read the rest