我有一个执行缓慢的查询。我知道使用依赖子查询是不好的,但是我想不出其他方法来获取我想要的数据。
基本上,我想标记过去6个月内至少有50张发票,但本月没有发票的客户。
这就是我目前所拥有的:
select
Customer.name,
Customer.id,
Customer.latitude,
Customer.longitude
from
Customer
where
EXISTS (
SELECT
*
FROM
Invoice_Header
WHERE
Invoice_Header.inv_date BETWEEN '2011-03-02' AND '2011-10-02'
AND
Invoice_Header.account_number = Customer.account_number
HAVING COUNT(invoice_num) > 50
)
AND NOT EXISTS (
SELECT *
FROM
Invoice_Header
WHERE
InvHead.inv_date > '2011-10-02'
AND
InvHead.account_number = Customer.account_number
)
Group by name;
Customer表有大约12k条记录,Invoice_Header有大约2条ill记录。
我有库存日期、帐号的索引(在两个表中)。
任何关于如何加快这一进程的建议都将不胜感激。
最佳答案
我建议:
SELECT
c.name,
c.id,
c.latitude,
c.longitude
FROM
Customer AS c
INNER JOIN (
SELECT account_number, count(*) AS invoice_count
FROM Invoice_Header
WHERE inv_date >= '2011-03-02' AND inv_date <= '2011-10-02'
GROUP BY account_number
) AS lsm
ON c.account_number = lsm.account_number
LEFT JOIN (
SELECT account_number, count(*) AS invoice_count
FROM Invoice_Header
WHERE inv_date > '2011-10-02'
GROUP BY account_number
) AS lm
ON c.account_number = lm.account_number
WHERE
lsm.invoice_count >= 50
AND IFNULL(lm.invoice_count, 0) = 0
关于mysql - 需要MySQL查询优化,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/7987135/