求一个sql语句的优化

[zhaowei273]

select fd_shortenedform,fd_spec, 
(  
select sum(number) as number from [order] 
where refund_status ='NO_REFUND' and (outer_iid = tb_goods.fd_spec or outer_sku_id = tb_goods.fd_spec)
)  as fd_number 
from tb_goods

主要是 那个 or 的条件，导致索引无效，查询效率有点低

如果没有聚合函数的话 可以用 union all ！现在有一个 sum的聚合函数 ，这个语句要如何优化，还请前辈们帮助下...

[Felixzhaowenzhong]

select fd_shortenedform,fd_spec, 
(  
select sum(number) as number from [order] 
where refund_status ='NO_REFUND' and (outer_iid = tb_goods.fd_spec or outer_sku_id = tb_goods.fd_spec)
)  as fd_number 
from tb_goods with(index(index_name))



--加个强行使用索引试一下。实在不行就分成两次然后再累加求和 SQL 语句的长短和效率不是成正比的

[zhaowei273]

二楼的这个语句好像不管用

有没有谁有办法帮解决下 实在不行的话，只能安照二楼的分两步了...

[zhaowei273]

select fd_shortenedform,fd_spec, 
(select sum(number) as number from [order] 
where refund_status ='NO_REFUND' and 
exists ( 
select fd_spec from tb_goods as t where tb_goods.fd_spec=[order].outer_sku_id
union all 
select fd_spec from tb_goods as t where tb_goods.fd_spec=[order].outer_iid
)
and tid in (SELECT tid FROM TradeList where (pay_time between '2012-5-01 0:00:00' and '2012-6-1 0:00:00') and type_status is null and status in('WAIT_SELLER_CHECK','WAIT_SELLER_SEND_GOODS', 'WAIT_BUYER_CONFIRM_GOODS','TRADE_BUYER_SIGNED','TRADE_FINISHED')
            union all
            SELECT tid FROM TradeList where (pay_time between '2012-5-01 0:00:00' and '2012-6-1 0:00:00') and type_status <> 0 and  status in('WAIT_SELLER_CHECK','WAIT_SELLER_SEND_GOODS', 'WAIT_BUYER_CONFIRM_GOODS','TRADE_BUYER_SIGNED','TRADE_FINISHED'))
) as fd_number
from tb_goods

这个sql语句 帮看看，有没有什么地方能优化下，效率提高点......

[昵称被占用了]

union all 和OR理论上功能是不同的，如果两个条件都符合的记录，union all会计算两次造成SUM虚增

[zhaowei273]

union all的效率确实比OR要高呀 你说的那样的话，那该怎么做呢？