mysql - SELECT 行中的子查询

Question

我正在尝试生成包含 EVENTSTOCKLINK 中的字段(如果存在)的 ACTION 记录列表我的查询过去看起来像

SELECT 
  action.actionid,
  partex.stockmake AS partexmake, 
  partex.stockmod AS partexmod
FROM
  ACTION 
  INNER JOIN EVENT 
    ON action.eventid = event.eventid 
  LEFT JOIN 
    (SELECT registrationnumber,stockmake,stockmod,stocktran,eventid 
    FROM eventstocklink 
  LEFT JOIN stock ON stock.stockid = eventstocklink.stockid 
WHERE statusid = '5'
GROUP BY eventstocklink.eventid
) AS partex 
ON partex.eventid = event.eventid 
WHERE actiondate2 BETWEEN 20130601 
  AND 20131031 
  AND event.siteid = 1 
  AND action.typeid = 1 

此查询需要 >4 秒 - 因此遵循 thread 的建议我一直在运行SELECT 行中的子查询，可以通过创建这样的查询来减少运行时间

SELECT action.actionid,
 (SELECT stockmake FROM eventstocklink
  LEFT JOIN stock ON stock.stockid = eventstocklink.stockid 
  WHERE statusid = '5' AND eventstocklink.eventid = event.eventid
 GROUP BY eventstocklink.eventid) partexmake,
 (SELECT stockmod FROM eventstocklink
  LEFT JOIN stock ON stock.stockid = eventstocklink.stockid 
  WHERE statusid = '5' AND eventstocklink.eventid = event.eventid
 GROUP BY eventstocklink.eventid) partexmod
FROM
  ACTION 
  INNER JOIN EVENT 
    ON action.eventid = event.eventid 
WHERE actiondate2 BETWEEN 20130601 
  AND 20131031 
  AND event.siteid = 1 
  AND action.typeid = 1  

这是构建此查询的最佳方式 - 我想包含 STOCK 表中的更多字段具有“statusid=5”

Answer 1

您的新查询具有相关的子查询，这些子查询往往性能不佳，而我不确定子查询在您的原始查询中是否必要。

了解表的索引会有所帮助，因为我怀疑这些是您当前查询困难的真正原因(例如，eventstocklink.eventid 上是否有索引)。

尝试这样的事情:-

SELECT 
  action.actionid,
  MAX(partex.stockmake) AS partexmake, 
  MAX(partex.stockmod) AS partexmod
FROM ACTION 
INNER JOIN EVENT 
ON action.eventid = event.eventid 
LEFT JOIN eventstocklink
ON eventstocklink.eventid = event.eventid
LEFT JOIN stock 
ON stock.stockid = eventstocklink.stockid 
AND stock.statusid = '5'
WHERE actiondate2 BETWEEN 20130601 AND 20131031 
AND event.siteid = 1 
AND action.typeid = 1 
GROUP BY action.actionid

这确实假设如果有多个匹配的，您实际上并不关心返回哪个 stockmake 或 stockmod (因为您现有的查询不会尝试定义返回哪些)。