Using a sub-query in WP instead of Inner Join-CodePudding

I've been investing tons of hours searching and applying many different tests to try and see if I could optimize this query but I was not successfull so far.

So this is based on a table with more than 6M entries and this query takes more than 2 seconds to perform:

SELECT DISTINCT b.meta_value as 'min'
FROM wp_postmeta as a
INNER JOIN wp_postmeta as b
ON a.post_id = b.post_id AND a.meta_key = 'Brand' AND b.meta_key = 'Yearofputtingintoproduction'
WHERE a.meta_value = 'BMW' 
ORDER BY b.meta_value DESC

And then I have this one that takes also more than 2 seconds to load:

SELECT DISTINCT b.meta_value
FROM wp_postmeta as a   
INNER JOIN wp_postmeta as b
ON a.post_id = b.post_id AND b.meta_key = 'OnlineModel'
INNER JOIN wp_postmeta as c
ON a.post_id = c.post_id AND c.meta_key = 'Brand' AND a.meta_key = 'Yearofputtingintoproduction'
INNER JOIN wp_postmeta as d
ON a.post_id = d.post_id AND d.meta_key = 'Yearofstoppingproduction'
WHERE  d.meta_key = 'Yearofstoppingproduction' AND d.meta_value >= '2020' AND a.meta_key = 'Yearofputtingintoproduction' AND a.meta_value <= '2021' AND c.meta_value = 'BMW'
ORDER BY b.meta_value ASC

What am I doing wrong here for this to take so long?

Any help will be much appreciated.

CodePudding user response：

You need the value from one table copy only, and you need to use DISTINCT for to remove duplicates. This leads to replace JOIN with WHERE EXISTS.

SELECT [DISTINCT] b.meta_value as 'min'
FROM wp_postmeta as b
WHERE EXISTS ( SELECT NULL
               FROM wp_postmeta as a
               WHERE a.post_id = b.post_id 
                 AND a.meta_key = 'Brand' 
                 AND a.meta_value = 'BMW' )
  AND b.meta_key = 'Yearofputtingintoproduction'
ORDER BY b.meta_value DESC

Whether DISTINCT can be deleted or not depends on the data.

CodePudding user response：

Have you installed https://wordpress.org/plugins/index-wp-mysql-for-speed/ ? That provides a generic fix for performance issues with "meta" tables in WP.

(This is an xample of where WP's EAV schema is especially ill-suited for the query in question. A simple table with brand, model, start_year, and stop_year would lead to a much simpler and intrinsically faster query.)