Question

数据量有100万，or连接导致效率很慢。有木于sql大神重新帮我组织一下。保留条件。谢谢了

SELECT COUNT(ID) AS tp_Count FROM shop_order_details D WHERE ( D.User_ID='4' AND D.Del_num=0 and D.Order_status =3 ) LIMIT 1 数据量有100万，or连接导致效率很慢。有木于sql大神重新帮我组织一下。保留条件。谢谢了
SELECT COUNT(ID) AS tp_Count FROM shop_order_details D WHERE ( D.User_ID='4' AND D.Del_num=0 and (D.Order_status = 3 or D.Order_status = 5) ) LIMIT 1 不好意思 or遗漏了

Answer 1

SELECT COUNT(ID) AS tp_Count FROM shop_order_details D WHERE ( D.User_ID='4' AND D.Del_num=0 and (D.Order_status = 3 or D.Order_status = 5) )
（1）User_ID、Del_num、Order_status 一定要建立索引，这个才是根本方法。
你这个查询条件，其他的提升应该不大
（2）建议可以union试试，SELECT COUNT(ID) AS tp_Count FROM shop_order_details D WHERE D.User_ID='4' AND D.Del_num=0 and D.Order_status = 3
union
SELECT COUNT(ID) AS tp_Count FROM shop_order_details D WHERE D.User_ID='4' AND D.Del_num=0 and D.Order_status = 5

Answer 2

你这个是对于单表的查询，实现过程只需要进行一次全表扫描，没有优化的价值了。要提升效率可以在User_ID，Del_num上建立排序索引。如果已经存在索引，就肯定建立数据库的时候分配的存储空间小，远不能存储100万数据，插入新数据的时候形成多个溢出表，扫描数据库的时候索引得不到有效利用，需要重新维护数据库。

Answer 3

哪里有or啊，都是and，你到底要做什么

追问

大神 我重新改了。下面那条 or遗漏了，帮帮我。。

大神 我重新改了。下面那条 or遗漏了，帮帮我。。

追答

用union替带了，写法如下：
select
count( ID ) as tp_Count
from
shop_order_details D
where
(
D.User_ID = '4'
and D.Del_num = 0
and D.Order_status = 3
)
union
select
count( ID ) as tp_Count
from
shop_order_details D
where
(
D.User_ID = '4'
and D.Del_num = 0
and D.Order_status = 5
) limit 1

追问

只能得到Order_status = 3 的count了。等于5 的没有加 怎么回事

追答

select
count( ID ) as tp_Count
from
shop_order_details D
where
(
D.User_ID = '4'
and D.Del_num = 0
and D.Order_status = 3
)
limit 1
union
select
count( ID ) as tp_Count
from
shop_order_details D
where
(
D.User_ID = '4'
and D.Del_num = 0
and D.Order_status = 5
) limit 1