第一个: 试试SELECT sum(case when p2c.category_id = '67' OR p2c.category_id = '66' OR p2c.category_id = '65' OR p2c.category_id = '64' OR p2c.category_id = '63' OR p2c.category_id = '62' OR p2c.category_id = '61' OR p2c.category_id = '54' then 1 else 0 end) AS total FROM product p inner join product_to_category p2c on p2c.product_id=p.product_id LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN product_special ps ON (p.product_id = ps.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0'
优化的方法反正 类似吧 把 p.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '63') 这种条件 尽量的不要放在where 里面啊
没有用内部函数啊 mysql好像 case when 是用 if else 这种来代替的 把sum里面改进下就可以了啊、 这些or条件放在where里面对于性能来说是很难受的
试试SELECT sum(case when p2c.category_id = '67'
OR p2c.category_id = '66' OR p2c.category_id = '65' OR p2c.category_id = '64'
OR p2c.category_id = '63' OR p2c.category_id = '62' OR p2c.category_id = '61'
OR p2c.category_id = '54' then 1 else 0 end) AS total
FROM product p inner join product_to_category p2c on p2c.product_id=p.product_id
LEFT JOIN product_description pd ON (p.product_id = pd.product_id)
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id)
LEFT JOIN product_special ps ON (p.product_id = ps.product_id)
WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW()
AND p2s.store_id = '0'
这些or条件放在where里面对于性能来说是很难受的
SQL Server数据库的代码优化实例:
http://database.51cto.com/art/201108/285253.htm