20 Jan

WordPress Meta Query Slow – WP_Query

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
ORDER BY wp_postmeta.meta_value+0 ASC
LIMIT 0, 32

The problem came to light looking at the query plan – the table wp_postmeta was using key meta_key and returning 292058 rows with information:
Using index condition; Using where; Using temporary; Using filesort

Removing that cast changed the query to use the same table, key, and query plan, but to only return 257 rows.… Read the rest