Question

I ve got a slow performing query. I know using a dependent subquery is bad, but I can t think of another way to get the data I want.

基本上,我要向过去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;

客户表有大约12k记录,Invoice_Header有大约2米记录。

我的指数为:日数,账户编号(两个表)。

希望能就如何加快这项工作提出建议。

Answer 1

我建议:

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

Answer 2

这应消除相关分局,并大大加快:

SELECT c.name, c.id, c.latitude, c.longitude
FROM Customer c
INNER JOIN (
  SELECT account_number
  FROM Invoice_Header ih
  WHERE ih.inv_date BETWEEN  2011-03-02  AND  2011-10-02 
  GROUP BY account_number
  HAVING COUNT(*) > 50
  MINUS
  SELECT DISTINCT account_number
  FROM Invoice_Header ih
  WHERE ih.inv_date >  2011-10-02 
) tbl
ON tbl.account_number = c.account_number

Answer 3

select
    C.name,
    C.id,
    C.latitude,
    C.longitude,
    I.account_number,         
    count( IF(I.inv_date>= 2011-03-02  AND I.inv_date <= 2011-10-02 ,I.inv_date,NULL )) as inv_count_6,
    count( IF(I.inv_date >  2011-10-02 ,I.inv_date,NULL )) as inv_count_1

from Customer C
LEFT JOIN Invoice_Header I
ON C.account_number = I.account_number 

GROUP BY C.id, I.account_number
HAVING inv_count_6 >= 50 AND inv_count_1=0
WHERE I.inv_date  BETWEEN  2011-03-02  AND  2011-10-02

注:

1. 导言发票是按英文第50条计算的,因此情况是“不”的;50。

2. 结论页: 1

Answer 4

Try与explain ,并且看看是否需要其他指数。

友情链接