mysql - 选择加载时间较少的sql语句

Question

我有 2 个 sql 语句，它们产生相同的结果，但想知道选择哪一个？

假设 1 有 3 个表:

1. 供应商
2. 供应商状态
3. supplier_contact

声明1)

SELECT a.*, b.status_name
  (SELECT c.name FROM  contact c 
   WHERE c.supplier_id = a.supplier_id 
   ORDER BY c.contact_id DESC limit 1) AS contact_name
FROM supplier a LEFT JOIN supplier_status b 
ON a.status_id = b.status_id 

声明2)

SELECT a.*, b.status_name, c.name AS contact_name 
FROM supplier a LEFT JOIN supplier_status b 
ON a.status_id = b.status_id 
LEFT JOIN (SELECT name, supplier_id 
   FROM contact  
   ORDER BY contact_id DESC
) c ON a.supplier_id = c.supplier_id
GROUP BY a.supplier_id 

Answer 1

尝试这个查询:

SELECT a.*, b.status_name, c.name AS contact_name
FROM supplier a
  LEFT JOIN supplier_status b ON a.status_id = b.status_id
  LEFT JOIN contact c ON a.supplier_id = c.supplier_id
  LEFT JOIN contact d ON c.supplier_id = d.supplier_id AND c.contact_id < d.contact_id
WHERE d.contact_id IS NULL

它可能不会产生与您相同的结果(我没有测试它)，但如果确实如此，那么您所要做的就是确保 JOIN 中出现的字段条件已编入索引(它们可能是 FK ，所以它们已经是)。