我有一个从表中查找特定客户的查询。
SELECT COUNT(*)
FROM CUSTOMER
WHERE amount <> 0
AND customerid = 22
因为customerid上有一个索引,所以DB扫描customerid = 22的所有行。
由于结果是通过检查计数是否返回零或大于零来处理的,那么如何优化查询呢?即,在金额为<> 0的第一个客户行,查询返回0,否则,如果所有行都=0,则返回1。
发布于 2011-02-18 00:14:27
select case
when exists (select *
from customer
where amount <> 0
and customerid = 22) then 1
else 0
end as non_zero_exists
发布于 2011-02-18 00:16:38
客户and和金额的第一个索引
CREATE INDEX customer_idx ON customer(customerid, amount);
然后将您的查询重写为
IF EXISTS (SELECT customerid
FROM customer
WHERE amount > 0 -- I am assuming here that amount cannot be a negative number.
AND customerid = 22)
SELECT 1
ELSE
SELECT 0
这应该会导致在customer_idx上进行索引查找。否则,您将需要扫描该客户的所有行(您的问题似乎暗示这可能很多)。
发布于 2011-02-18 00:22:58
似乎足够直截了当
IF EXISTS ( SELECT customerid
FROM customer
WHERE amount <> 0
and customerid = 22))
SELECT 1
ELSE
SELECT 0
https://stackoverflow.com/questions/5031471
复制相似问题