explain
SELECT 
    CASE WHEN p_tr.id IS NULL THEN p_tr.object_id ELSE p_tr.id END AS product_id, 
    p_tr.object_id AS parent_product_id, 
    p_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 
  (

    SELECT 
      p.id,
      coalesce(tr1.term_taxonomy_id,tr2.term_taxonomy_id) as term_taxonomy_id,
      coalesce(tr1.object_id,tr2.object_id) as object_id
    FROM 
          wp_posts p
      LEFT JOIN
          wp_term_relationships tr1 ON (p.id = tr1.object_id) AND 
                                      p.post_type LIKE '%product%' AND 
                                      p.post_status = 'publish'
      LEFT JOIN 
          wp_term_relationships tr2 ON (p.post_parent = tr2.object_id) AND 
                                      p.post_type LIKE '%product%' AND 
                                      p.post_status = 'publish'
    WHERE
      tr1.object_id is not null or tr2.object_id is not null
  ) AS p_tr

  JOIN 
      wp_term_taxonomy xt ON xt.term_taxonomy_id = p_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_tr.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_tr.id = tc.object_id
GROUP BY 
    p_tr.id, p_tr.object_id, t.term_id, t.name, t.slug
ORDER BY 
    p_tr.id, t.slug DESC
;
