WordPress custom meta query very slow AND relation

How many times are you calling this function per page load? Do you need pagination? Here’s a 10up article about some query optimization practices but I don’t think anything there will bring it down from 10-20 seconds to 1-2.

If you’re calling it for the same car version multiple times per page you could cache the results via WP_Object_Cache.

You could also store the results in a transient via Transient API which you could set to expire once a month/week but if you’re calling this function for multiple different car version per page it would bog down your options table and I wouldn’t suggest using transients.

Hi @howdy_mcgee
Thank you for your answer.

I only call this function once per page load.

I don’t need pagination.

Could it be cause behind there are some INNER JOINs within the same table?

I am lost

If your post_meta table is pretty big or you have a large amount of posts ( you are returning ALL ( -1 ) then it may take awhile. You’re joining on the posts table and postmeta table.

If you don’t need pagination, here’s some optimized args

$args = array(
	'post_type' 	=> 'version',
	'post_status'	=> array( 'publish' ),	// Only look for published
	'no_found_rows'	=> true,				// Don't calculate pagination
	'posts_per_page'=> 400,					// Pull a max of 400 at any given time
	'meta_query' 	=> array(
		'relation' => 'AND',
		array(
			'key' => '_wpcf_belongs_car_id',
			'value' => $car_id,
		),
		'power-clause' => array(
			'key' 		=> 'wpcf-power',
			'compare' 	=> 'EXISTS',
			'type' 		=> 'numeric',
		),
		'gear-clause' => array(
			'key' 		=> 'wpcf-gear',
			'compare' 	=> 'EXISTS',
			'type'		=> 'numeric',
		),
	),
	'orderby' => array(
		'power-clause' => 'ASC',
		'gear-clause' => 'ASC',
	),
);

This may not even improve performance. If you really want to see the SQL it’s generating you can throw it into a new WP_Query and print the SQL:

$results = new WP_Query( $args );
error_log( $results->request );

You would need to enable WP Debug and WP Debug Log via wp-config.php. This will add the SQL of that query to the debug file -> /wp-content/debug.log.

https://codex.www.ads-software.com/Debugging_in_WordPress

Thank you again @howdy_mcgee
I tried those parameters but didn’t seem to improve performance. I have displayed the SQL query that is being generated and is this:

SELECT cochewp_posts.ID FROM cochewp_posts INNER JOIN cochewp_postmeta ON ( cochewp_posts.ID = cochewp_postmeta.post_id ) INNER JOIN cochewp_postmeta AS mt1 ON ( cochewp_posts.ID = mt1.post_id ) INNER JOIN cochewp_postmeta AS mt2 ON ( cochewp_posts.ID = mt2.post_id ) WHERE 1=1 AND ( ( cochewp_postmeta.meta_key = '_wpcf_belongs_car_id' AND cochewp_postmeta.meta_value = '84431' ) AND mt1.meta_key = 'wpcf-power' AND mt2.meta_key = 'wpcf-gear' ) AND cochewp_posts.post_type = 'version' AND ((cochewp_posts.post_status = 'publish')) GROUP BY cochewp_posts.ID ORDER BY CAST(mt1.meta_value AS SIGNED) ASC, CAST(mt2.meta_value AS SIGNED) ASC LIMIT 0, 400

I see that is doing the samme INNER JOIN 3 times with different alias and then uses all of them in the WHERE clause. This sounds weird to me, shouldn’t it be a single INNER JOIN? What do you think?