MariaDB [test]> analyze SELECT CASE WHEN p.id IS NULL THEN tr.object_id ELSE p.id END AS product_id, tr.object_id AS parent_product_id, tr.object_id AS effective_product_id, t.term_id as brand_id, t.name AS brand, t.slug as brand_slug, '0' as product_stock, '0' as product_count, MAX(ts.name) as size, MAX(tc.name) as color FROM wp_posts p JOIN wp_term_relationships tr ON (p.id = tr.object_id OR p.post_parent = tr.object_id) AND p.post_type LIKE '%product%' AND p.post_status = 'publish' JOIN wp_term_taxonomy xt ON xt.term_taxonomy_id = tr.term_taxonomy_id AND xt.taxonomy = 'pa_brand_master' JOIN wp_terms t ON t.term_id = xt.term_id LEFT JOIN (SELECT trs.object_id, ts.name FROM wp_term_relationships trs JOIN wp_term_taxonomy xs ON xs.term_taxonomy_id = trs.term_taxonomy_id JOIN wp_terms ts ON ts.term_id = xs.term_id WHERE xs.taxonomy IN ('pa_size', 'pa_megethos', 'pa_noumero') ) AS ts ON p.id = ts.object_id LEFT JOIN (SELECT trc.object_id, tc.name FROM wp_term_relationships trc JOIN wp_term_taxonomy xc ON xc.term_taxonomy_id = trc.term_taxonomy_id JOIN wp_terms tc ON tc.term_id = xc.term_id WHERE xc.taxonomy IN ('pa_color', 'pa_chroma', 'pa_colour') ) AS tc ON p.id = tc.object_id GROUP BY p.id, tr.object_id, t.term_id, t.name, t.slug ORDER BY p.id, t.slug DESC; +------+-------------+-------+--------+-----------------------------------+------------------+---------+---------------------------+-------+----------+----------+------------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+-------+--------+-----------------------------------+------------------+---------+---------------------------+-------+----------+----------+------------+-----------------------------------------------------------+ | 1 | SIMPLE | p | index | PRIMARY,post_parent | post_parent | 172 | NULL | 52700 | 58013.00 | 100.00 | 25.78 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | trs | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | test.p.ID | 4 | 4.92 | 100.00 | 100.00 | Using where | | 1 | SIMPLE | xs | eq_ref | PRIMARY,term_id_taxonomy,taxonomy | PRIMARY | 8 | test.trs.term_taxonomy_id | 1 | 1.00 | 0.04 | 0.00 | Using where | | 1 | SIMPLE | ts | eq_ref | PRIMARY | PRIMARY | 8 | test.xs.term_id | 1 | NULL | 100.00 | NULL | | | 1 | SIMPLE | trc | ref | PRIMARY,term_taxonomy_id | PRIMARY | 8 | test.p.ID | 4 | 4.92 | 100.00 | 100.00 | Using where | | 1 | SIMPLE | xc | eq_ref | PRIMARY,term_id_taxonomy,taxonomy | PRIMARY | 8 | test.trc.term_taxonomy_id | 1 | 1.00 | 0.34 | 0.21 | Using where | | 1 | SIMPLE | tc | eq_ref | PRIMARY | PRIMARY | 8 | test.xc.term_id | 1 | 1.00 | 100.00 | 100.00 | | | 1 | SIMPLE | xt | ref | PRIMARY,term_id_taxonomy,taxonomy | taxonomy | 130 | const | 254 | 254.00 | 100.00 | 100.00 | Using index condition | | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 8 | test.xt.term_id | 1 | 1.00 | 100.00 | 100.00 | | | 1 | SIMPLE | tr | ref | PRIMARY,term_taxonomy_id | term_taxonomy_id | 8 | test.xt.term_taxonomy_id | 9 | 31.18 | 100.00 | 0.01 | Using where; Using index | +------+-------------+-------+--------+-----------------------------------+------------------+---------+---------------------------+-------+----------+----------+------------+-----------------------------------------------------------+ 10 rows in set (26.430 sec) MariaDB [test]> select version(); +--------------------+ | version() | +--------------------+ | 11.5.0-MariaDB-log | +--------------------+ 1 row in set (0.000 sec) p, (trs, xs ts), (trc, xc, tc) xt t tr