两条开源的SQL优化。

[剑心永远OK]

下面是两条开源代码的SQL。。大家看看还能不能优化。。
1:计算每个类别下面有多少产品

SELECT COUNT(*) AS total FROM product p 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' AND p.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE 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')

建立索引的表
product表：
主键：product_id
联合索引：date_available,sort_order,status
单索引：date_available
单索引：sort_order
单索引：status

product_description表：
联合主键：product_id, language_id
索引：name

product_to_shops表：
联合索引：product_id,shops_id
单索引：shops_id

product_to_category表：
联合索引：product_id,category_id
单索引：category_id


2:

SELECT p.product_id, (SELECT AVG(rating) AS total 
FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating 
FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) 
LEFT JOIN product_special ps ON (p.product_id = ps.product_id) 
LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) 
WHERE pd.language_id = '1' 
AND p.status = '1' 
AND p.date_available <= NOW() 
AND p2s.store_id = '0' 
AND p.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '63') ORDER BY p.sort_order DESC LIMIT 0,35

基本所引同上。
review表
索引：product_id

以上两条语句在本机测试没问题，但在服务器上面，记录为慢查询。不知道为何。。

[oO寒枫Oo]

第一个：
试试

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' 

[oO寒枫Oo]

我晕 你的貌似是mysql吧

[oO寒枫Oo]

优化的方法反正 类似吧 把 p.product_id IN (SELECT p2c.product_id FROM product_to_category p2c WHERE p2c.category_id = '63')

这种条件 尽量的不要放在where 里面啊

[剑心永远OK]

用常规的优化方法有没，不想使用过多的内部函数处理撒。

[oO寒枫Oo]

没有用内部函数啊 mysql好像 case when 是用 if else 这种来代替的 把sum里面改进下就可以了啊、
这些or条件放在where里面对于性能来说是很难受的

[剑心永远OK]

我就感觉这种写法纠结。。

有其他方式不？

[--小F--]

第一个那么多的OR 如何优化啊?

[lxfjoy]

资源共享：
SQL Server数据库的代码优化实例：
http://database.51cto.com/art/201108/285253.htm