MySQL 查询重新设计

Question

我有以下查询，从 notes 表中选择所有内容，其中输入(例如:bob)不在 orders 表中。

SELECT * FROM `notes` WHERE notes.customer_email NOT IN 
(SELECT customers_email_address FROM orders) 
AND ((customer_phone LIKE '%bob%') 
OR (customer_name LIKE '%bob%') 
OR (customer_email LIKE '%bob%')) 
AND customers_id IS NULL 
GROUP BY `customer_email` 
ORDER BY `customer_name` 
DESC LIMIT 50

这个查询的胖男孩在我的开发机器上花费了大约 80 秒，在实时服务器上花费了大约 7 秒。

两个问题:

1. 我在这个查询中做错了什么？ (我需要从错误中吸取教训)
2. 如何改进此查询？

Answer 1

尝试使用连接:

SELECT *
FROM
  `notes` left join orders
   on notes.customer_email=orders.customers_email_address
WHERE
  orders.customers_email_address is null
  AND notes.customers_id IS NULL 
  AND ((customer_phone LIKE '%bob%') 
        OR (customer_name LIKE '%bob%') 
        OR (customer_email LIKE '%bob%')) 
ORDER BY `customer_name` 
DESC LIMIT 50

它们通常比 IN/NOT IN 子句更快。而且，我不确定为什么要在此处放置 group by 子句。