Slow WordPress WP_Query with Woocommerce

I just created a WP_Query query for a WordPress Website that took multiple minutes and had not returned any results yet and then fixed it to run within a second or so. This is probably not a common requirement for your average WordPress user. But I thought it might be useful for anyone else putting an OR condition on WordPress’s custom fields.

I’m working on a WooCommerce solution for a customer and needed to display products with two custom fields (width and height) falling between 2 mix-max ranges OR, if another custom field on a products was set, then to check whether the width falls within the height min-max range and the height falls in the width’s min-man range.

Because WordPress holds all your custom fields in the same database table (wp_postmeta), the resulting MySQL query gets pretty hairy very quickly when you do an OR condition like that. For every condition, WP_Query ends up joining to a new instance of wp_postmeta and every join slows your query down a little more.

Here’s the MySQL query that was generated for my original WP_Query and as you can see, it’s joining 10 times to wp_postmeta

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 )
INNER JOIN wp_postmeta AS mt2 ON ( wp_posts.ID = mt2.post_id )
INNER JOIN wp_postmeta AS mt3 ON ( wp_posts.ID = mt3.post_id )
INNER JOIN wp_postmeta AS mt4 ON ( wp_posts.ID = mt4.post_id )
INNER JOIN wp_postmeta AS mt5 ON ( wp_posts.ID = mt5.post_id )
INNER JOIN wp_postmeta AS mt6 ON ( wp_posts.ID = mt6.post_id )
INNER JOIN wp_postmeta AS mt7 ON ( wp_posts.ID = mt7.post_id )
INNER JOIN wp_postmeta AS mt8 ON ( wp_posts.ID = mt8.post_id )
INNER JOIN wp_postmeta AS mt9 ON ( wp_posts.ID = mt9.post_id )
WHERE 1=1 AND
(
( wp_postmeta.meta_key = ‘_bywmasalesprice’ AND CAST(wp_postmeta.meta_value AS SIGNED) <= ‘5000’ ) AND
(
(
( mt1.meta_key = ‘_maheight’ AND CAST(mt1.meta_value AS SIGNED) >= ‘0’ ) AND
( mt2.meta_key = ‘_maheight’ AND CAST(mt2.meta_value AS SIGNED) <= ‘300’ ) AND
( mt3.meta_key = ‘_mawidth’ AND CAST(mt3.meta_value AS SIGNED) >= ‘0’ ) AND
( mt4.meta_key = ‘_mawidth’ AND CAST(mt4.meta_value AS SIGNED) <= ‘300’ )
)
OR
(
( mt5.meta_key = ‘_mainterchange_orientation’ AND CAST(mt5.meta_value AS CHAR) = ‘on’ ) AND
( mt6.meta_key = ‘_mawidth’ AND CAST(mt6.meta_value AS SIGNED) >= ‘0’ ) AND
( mt7.meta_key = ‘_mawidth’ AND CAST(mt7.meta_value AS SIGNED) <= ‘300’ ) AND
( mt8.meta_key = ‘_maheight’ AND CAST(mt8.meta_value AS SIGNED) >= ‘0’ ) AND
( mt9.meta_key = ‘_maheight’ AND CAST(mt9.meta_value AS SIGNED) <= ‘300’ )
)
)
) AND
wp_posts.post_type = ‘product’ AND
…

Obviously this is a problematic query and joining that many times to the same table is bound to be slow. Currently I only have about 10 products in my development database, but I have not had enough patience to wait for this query to return successfully. Each time I’ve cancelled it after a few minutes.

Use BETWEEN where appropriate

My first attempt was to replace >= and <= comparisons with BETWEEN comparisons. In my meta_query I had statements like this:

array(
‘key’ => “_mawidth”,
‘value’ => $widthmin,
‘compare’ => “>=”,
‘type’ => “numeric”
),
array(
‘key’ => “_mawidth”,
‘value’ => $widthmax,
‘compare’ => “<=”,
‘type’ => “numeric”
)

So just to check the width in one of the ranges, WP_Query was joining to wp_postmeta twice. Instead, I combined those two conditions into a single BETWEEN condition like this:

array(
‘key’ => “_bywmawidth”,
‘value’ => array($widthmin, $widthmax),
‘compare’ => “BETWEEN”,
‘type’ => “numeric”
),

changing the comparisons to BETWEEN improved the query considerably and you can see it dropped the number of joins to “only” 6

It also made a big improvement on the performance and the page now actually returned before I ran out of patience. However, it still took 8.95 seconds (approx. the same for multiple tests) to return the query in my local environment with a very small dataset and nobody else querying the database. While it’s a massive improvement (infinity down to 9 seconds 🙂 ), it still wouldn’t cut it in a production environment. Nobody wants to wait 9 seconds for a query to return, nevermind that this page is returning an image for every product which will take time to download as well.

Running multiple queries

MySQL was having a hard time dealing with the multiple joins to the same table and while using BETWEEN improved that somewhat, I decided to help WP_Query a little. The problem, as I mentioned at the outset is the OR condition. It’s effectively doing the same checks to see whether width and height are within certain ranges twice and therefore joining to wp_postmeta twice as many times. Simple solution, I decided to run the query twice and combine the results instead. I won’t paste all my code here, but basically, you call WP_Query twice putting the results into $loopQry1 and $loopQry2 and then use something similar to this to combine the results before your loop.

$loopQryMain->posts = array_merge($loopQry1->posts, $loopQry2->posts);
$loopQryMain->post_count = $loopQry1->post_count + $loopQry2->post_count;

For my situation that didn’t work as I ended up with duplicates as width and height of a particular product could possibly fall into both min-max ranges, so instead I looped through $loopQry2’s posts and added them to $loopQry1 if they weren’t already there, but you get the idea.

The rest? Running both queries and combining their results took 0.009 seconds in my local environment. That’s more like it, isn’t it? It’s not the cleanest code out there, but for those type of performance improvements, I’m happy to add some comments to my code to help explain it to the next guy!