标题: 向大家请教几个mysql优化，学习了 [打印本页] 作者: xiaopingguoer 时间: 2011-10-11 14:05 标题: 向大家请教几个mysql优化，学习了 第一条：SELECT o.seller_memo,o.order_id, o.order_sn, o.add_time, o.order_status, o.shipping_status, o.order_amount, o.total_amount,o.deal_status,o.confirm_status,o.payment_status,o.payment_id, o.consignee, o.email, o.order_code, o.order_memo, o.order_flag,o.is_error,o.store_id,o.refunds_type,s.store_name, IFNULL(u.user_name, '匿名用户') AS user_name 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
第二条：SELECT g.goods_id, g.goods_name, g.goods_sn, g.promote_price, g.shop_price, g.cost_price, g.market_price, g.is_on_sale, g.is_best, g.is_new, g.is_hot, g.is_index, g.style_sort, g.goods_number,g.goods_number_A, g.integral, SUM(og.goods_number) AS sell_count, g.click_count, g.quota, (g.promote_price > 0 AND g.promote_start_date <= '1317225599' AND g.promote_end_date >= '1317225599') AS is_promote, g.sort_order, FROM_UNIXTIME(g.add_time, '%Y-%m-%d') AS add_date, g.goods_thumb, g.goods_class,g.up_down_tip 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作者: kider 时间: 2011-10-13 10:01
乱码七糟，都不想看。
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 时间: 2011-10-14 06:11 标题: 第一个SQL语句的点评 本帖最后由 je_ck 于 2011-10-14 06:11 编辑
好像你所有操作都是围绕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
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 时间: 2011-10-14 06:14
像第二个就是比较简单的。如果你对性能不满意，我认为只有添加索引来解决了。