mysql 优化同一表的多个子查询

我有一个查询，其中同一个表上有多个子查询。我尝试将它们转换为左联接，但这比此查询花费更多时间。
该表为：产品类别链接
如果有人能帮我解决任何问题，那就太好了。
先谢了

select pc.id as id,
       pc.code,
       pc.name,
       pc.deliver_from,
       pc.delivery,
       pc.is_active,
       1 as results,
       cr.ship_date_advance, 
       p.callouts, 
       p.coid,
       p.c_name, 
       pc.start_expire_date, 
       pc.end_expire_date
from product_collection pc
join (select p.product_collection_id, 
             pdc.id as coid, pdc.name as c_name,
             count(distinct pcl.product_id) as callouts, pi.quantity
      from product p
      join product_color pclr on pclr.product_id=p.id 
      join product_collection pc on pc.id=p.product_collection_id and pc.client_id = 142 and pc.active='True'
      left join product_gender_link pgl on pgl.product_id=p.id
      left join product_callout_link pcl on pcl.product_collection_id=pc.id and pcl.product_id=p.id
      left join product_callout pdc on pdc.id=pcl.product_callout_id and pdc.client_id=142
      left join product_inventory pi on pi.product_color_id=pclr.id and pi.quantity>0 
      left join product_price_link ppl on ppl.product_id=p.id
      LEFT JOIN product_class_link pdcl ON pdcl.product_id = p.id
      where ppl.product_price_id in (447,249,456) 
      and p.active = 'True'
      and pgl.product_gender_id = 411
      AND ( ( product_sub_class_id = 10 AND pdcl.product_id IN ( select DISTINCT product_id 
                                                                 FROM product_class_link 
                                                                 WHERE  product_sub_class_id = 51 
                                                                 OR  product_sub_class_id = 53 
                                                                 OR  product_sub_class_id = 55 
                                                                 ) 
            )  
            OR  ( product_sub_class_id = 11 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 OR  product_sub_class_id = 55 )  )  
            OR  ( product_sub_class_id = 12 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 OR  product_sub_class_id = 53 OR  product_sub_class_id = 55 )  )  
            OR  ( product_sub_class_id = 14 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 OR  product_sub_class_id = 52 OR  product_sub_class_id = 55 )  )  
            OR  ( product_sub_class_id = 13 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 ))  
            OR  ( product_sub_class_id = 15 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 OR  product_sub_class_id = 52 OR  product_sub_class_id = 55 )  )  
            OR  ( product_sub_class_id = 24 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 )  )  
            OR  ( product_sub_class_id = 29 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 )  )  
            OR  ( product_sub_class_id = 25 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 )  )  
            OR  ( product_sub_class_id = 30 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 )  )  
            OR  ( product_sub_class_id = 20 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 )  )  
            OR  ( product_sub_class_id = 23 AND pdcl.product_id IN (select DISTINCT product_id FROM product_class_link WHERE  product_sub_class_id = 51 )  )  
            )
        and (pc.is_active < 2 or pi.quantity IS NOT NULL)
        group by pc.id, pdc.id
      ) as p on p.product_collection_id=pc.id
left join client_shipdate_rules cr on cr.product_collection_id=pc.id
where  pc.active='True'
and pc.client_id = 142
group by pc.id, p.coid
order by pc.position,pc.name, pc.deliver_from, p.coid

以下是解释查询输出：

OR难以优化;看看能不能避免。
IN ( SELECT ... ) is usually bad for performance. It can sometimes be turned into a联接or存在（选择...）. This is especially valid since you have区分“。
看看是否可以将其从WHERE移到JOIN中：

SELECT  DISTINCT product_id
                FROM  product_class_link
                WHERE  product_sub_class_id = 51 )

以下一些索引可能会有所帮助：

PC:   INDEX(active, client_id, id)
cr:   INDEX(product_collection_id,  ship_date_advance)
pcl:  INDEX(product_collection_id,  product_id, product_callout_id)
pi:   INDEX(quantity, product_color_id)
pclr: INDEX(product_id,  id)
PC:   INDEX(is_active, id,  client_id, active)
pgl:  INDEX(product_gender_id, product_id)
ppl:  INDEX(product_price_id, product_id)
pdcl: INDEX(product_id)

警告：由于某些别名用于多个表，因此上述内容可能是“错误的”。（例如，p。）
product_sub_class_id在哪个表中？
如果你不需要LEFT，就不要说出来。

mysql 优化同一表的多个子查询

1条答案

相关问题

热门标签

最新问答