向大家请教几个mysql优化，学习了

kider

乱码七糟，都不想看。

有好逻辑使语句简单。
多用用explian
检查索引
尽量少用in
...

je_ck

SELECT *
FROM `pb89_online`.`mf_order` AS o  
        LEFT JOIN
`pb89_online`.`mf_users` AS u
ON u.user_id = o.user_id
        LEFT JOIN `pb89_online`.`mf_store` AS s
ON s.store_id = o.store_id
WHERE 1 =1  AND
o.payment_status = 2 AND
short_time in (20110928,20110927,20110926,20110925,20110924,20110923,20110922) AND
o.store_id = '28' AND
order_status  IN ('1','2')  AND
shipping_status   IN ('0','1')  
ORDER BY o.add_time DESC
LIMIT 0, 90

je_ck

好像你所有操作都是围绕o（`pb89_online`.`mf_order`）来做的。
left join 感觉作用不大。
你可以考虑这样写
SELECT *
FROM (select * from `pb89_online`.`mf_order` WHERE 1 =1  AND
o.payment_status = 2
AND short_time in (20110928,20110927,20110926,20110925,20110924,20110923,20110922)
AND o.store_id = '28' AND order_status  IN ('1','2')  AND shipping_status   IN ('0','1')  
ORDER BY o.add_time DESC LIMIT 0, 90
)AS o  
        LEFT JOIN
`pb89_online`.`mf_users` AS u
ON u.user_id = o.user_id
        LEFT JOIN `pb89_online`.`mf_store` AS s
ON s.store_id = o.store_id

对于想通过联合过滤的，可以采用定时删除的方式。

je_ck

SELECT *
FROM `pb89_online`.`mf_goods` AS g
        LEFT JOIN
`pb89_online`.`mf_order_goods` AS og
ON og.goods_sn = g.goods_sn  
WHERE is_delete='0'  AND g.is_real='1' GROUP BY g.goods_id
ORDER BY is_on_sale DESC

je_ck

像第二个就是比较简单的。如果你对性能不满意，我认为只有添加索引来解决了。