我这有2张表 t_order 和 t_refundt_order 表结构大致如下 大约有4w条数据 将来可能更多
t_refund表结构大致如下 大约2w条数据
我有下面这个查询需求SELECT
count(*)
FROM
t_order o
LEFT JOIN t_refund r ON o.id = r.order_id
WHERE
(
(
o.balance_date >= '2016-06-29 00:00:00.0'
AND o.balance_date < '2016-06-30 00:00:00.0'
)
OR (
r.refund_date >= '2016-06-29 00:00:00.0'
AND r.refund_date < '2016-06-30 00:00:00.0'
)
)
AND o.order_status IN (3, 6)
AND o.supplier_id = 5
执行这条语句目前大约要10s+ 效率好低啊 有没有大神可以帮我优化一下 或者给个建议
t_refund表结构大致如下 大约2w条数据
我有下面这个查询需求SELECT
count(*)
FROM
t_order o
LEFT JOIN t_refund r ON o.id = r.order_id
WHERE
(
(
o.balance_date >= '2016-06-29 00:00:00.0'
AND o.balance_date < '2016-06-30 00:00:00.0'
)
OR (
r.refund_date >= '2016-06-29 00:00:00.0'
AND r.refund_date < '2016-06-30 00:00:00.0'
)
)
AND o.order_status IN (3, 6)
AND o.supplier_id = 5
执行这条语句目前大约要10s+ 效率好低啊 有没有大神可以帮我优化一下 或者给个建议
explain select ...
show index from ..
以供分析。
你的t_order 表有索引吗
FROM (SELECT Count(*) AS cnt
FROM t_order o
LEFT JOIN t_refund r
ON o.id = r.order_id
WHERE o.balance_date >= '2016-06-29 00:00:00.0'
AND o.balance_date < '2016-06-30 00:00:00.0'
AND o.order_status IN ( 3, 6 )
AND o.supplier_id = 5
UNION ALL
SELECT Count(*)
FROM t_order o
LEFT JOIN t_refund r
ON o.id = r.order_id
WHERE r.refund_date >= '2016-06-29 00:00:00.0'
AND r.refund_date < '2016-06-30 00:00:00.0'
AND o.order_status IN ( 3, 6 )
AND o.supplier_id = 5) a; o.balance_date
r.refund_date
o.id
r.order_id
看有没有索引,如果没有就创建索引,主键就不用了