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.

The key “wp_postmeta.meta_key” has three parts: meta_key, meta_value (100), and post_id
And it appears the cast was stopping MySQL from using the “meta_value” part of the index. By removing the cast, MySQL could use both meta_key and meta_value, and therefore avoid returning too many rows.

Luckily enough, WordPress provides a filter to allow access to the meta query string – “get_meta_sql”. Using the code below in your functions.php file you can remove that unnecessary cast and speed up your queries:


/**
* Remove casting a meta_query to a char as this stops it using the index!
*/
function my_filter_meta_query( $pieces ) {

if ( !empty( $pieces['where'] ) ) {
// remove casting to CHAR as this is already a string
$pieces['where'] = preg_replace("@CAST\(([^.]*.meta_value) AS CHAR\)@", "$1",$pieces['where']);
}
return $pieces;
}
add_filter( 'get_meta_sql', 'my_filter_meta_query' );

Leave a Reply

Your email address will not be published. Required fields are marked *