mysql - SQL通过电子邮件字段获取一次性客户

Question

我有一个包含超过 100,000 条记录的数据库。我正在尝试获取仅通过客户电子邮件字段 (OrderEmail) 搜索一次的所有客户。

SQL 查询运行了 10 分钟，然后超时。

如果我使用短日期范围，我可以获得结果，但仍然需要 3 分钟以上。

如何优化语法以使其正常工作？

SELECT 
    tblOrders.OrderID,
    tblOrders.OrderName,
    tblOrders.OrderEmail,
    tblOrders.OrderPhone,
    tblOrders.OrderCountry,
    tblOrders.OrderDate
FROM
    tblOrders
LEFT JOIN tblOrders AS orders_join ON orders_join.OrderEmail = tblOrders.OrderEmail
    AND NOT orders_join.OrderID = tblOrders.OrderID
WHERE
    orders_join.OrderID IS NULL
    AND (tblOrders.OrderDate BETWEEN '2015-01-01' AND '2017-03-01')
    AND tblOrders.OrderDelivered = - 1
ORDER BY
    tblOrders.OrderID ASC;

Answer 1

我希望以下内容能够工作 - 但我无法测试它，因为您不提供示例数据。好吧，我添加了一个可用于查询的临时表定义....

但是，如果您实际上可以更改数据模型，为下订单的实体使用 INTEGER id(而不是 VARCHAR() 电子邮件地址)，那么您的速度会快得多。

CREATE TEMPORARY TABLE IF NOT EXISTS
tblorders(orderid,ordername,orderemail,orderphone,ordercountry,orderdate) AS (
            SELECT  1,'ORD01','adent@hog.com' ,'9-991' ,'UK', DATE '2017-01-01'
  UNION ALL SELECT  2,'ORD02','tricia@hog.com','9-992' ,'UK', DATE '2017-01-02'
  UNION ALL SELECT  3,'ORD03','ford@hog.com'  ,'9-993' ,'UK', DATE '2017-01-03'
  UNION ALL SELECT  4,'ORD04','zaphod@hog.com','9-9943','UK', DATE '2017-01-04'
  UNION ALL SELECT  5,'ORD05','marvin@hog.com','9-9942','UK', DATE '2017-01-05'
  UNION ALL SELECT  6,'ORD06','ford@hog.com'  ,'9-993' ,'UK', DATE '2017-01-06'
  UNION ALL SELECT  7,'ORD07','tricia@hog.com','9-992' ,'UK', DATE '2017-01-07'
  UNION ALL SELECT  8,'ORD08','benji@hog.com' ,'9-995' ,'UK', DATE '2017-01-08'
  UNION ALL SELECT  9,'ORD09','benji@hog.com' ,'9-995' ,'UK', DATE '2017-01-09'
  UNION ALL SELECT 10,'ORD10','ford@hog.com'  ,'9-993' ,'UK', DATE '2017-01-10'
)
;

SELECT 
  tblOrders.OrderID
, tblOrders.OrderName
, tblOrders.OrderEmail
, tblOrders.OrderPhone
, tblOrders.OrderCountry
, tblOrders.OrderDate
FROM tblOrders
JOIN (
  SELECT
      OrderEmail
    FROM tblOrders
    GROUP BY
      OrderEmail
    HAVING COUNT(*) = 1
) singleOrders
ON singleOrders.OrderEmail = tblOrders.OrderEmail
    ORDER BY OrderID
;

OrderID|OrderName|OrderEmail    |OrderPhone|OrderCountry|OrderDate
      1|ORD01    |adent@hog.com |9-991     |UK          |2017-01-01
      4|ORD04    |zaphod@hog.com|9-9943    |UK          |2017-01-04
      5|ORD05    |marvin@hog.com|9-9942    |UK          |2017-01-05

如您所见，它返回 Mr. Dent、Zaphod 和 Marvin，他们在示例数据中仅出现一次。