MySql 尝试提高查询性能但在列上出现错误

Question

这实际上是两个问题合而为一，但我会尽力使其尽可能切中要点。

我向一位 friend 展示了我的初始查询，他告诉我可以通过子选择进一步提高性能(在此处使用 MySql)。

不幸的是，我无法让他的查询工作，因为我有一个错误代码:1054
“where 子句”中的未知列“e.id”

他的查询如下:如果有人告诉我为什么它无法识别 e.id，我将不胜感激。

SELECT e.id AS event_id, er.name AS event_name, er.revision AS revision, er.start_date AS event_dates
 FROM `events` AS e
 INNER JOIN 
 (
    SELECT event_id, revision, start_date 
    FROM event_revisions
    WHERE YEAR(start_date) = YEAR(CURDATE())
    AND revision = (
       SELECT MAX(er_tmp.revision) 
       FROM event_revisions AS er_tmp
       WHERE er_tmp.approved = 1
       AND e.id = er_tmp.event_id
    )
 ) AS er ON (e.id = er.event_id)
 INNER JOIN member_events AS me ON (me.event_id = e.id)
 WHERE 
  e.enabled = 1 AND 
  e.deleted = 0
 GROUP BY event_id
 ORDER BY event_dates

这是我原来的查询:

SELECT e.id AS event_id, er.name AS event_name, er.revision AS revision, er.start_date AS event_dates
FROM `events` AS e
INNER JOIN event_revisions AS er ON (e.id = er.event_id)
INNER JOIN member_events AS me ON (me.event_id = e.id)
WHERE 
    e.enabled = 1 AND 
    e.deleted = 0 AND 
    er.revision = (
        SELECT MAX(er_tmp.revision) 
        FROM event_revisions AS er_tmp
        WHERE er_tmp.approved = 1
        AND  e.id = er_tmp.event_id
    )
    AND YEAR(er.start_date) = YEAR(CURDATE())
GROUP BY event_id
ORDER BY event_dates

第二个问题是:为什么他的查询比我的快？

任何意见都将受到高度赞赏。

Answer 1

AD 1.因为子选择:

 SELECT MAX(er_tmp.revision) 
    FROM event_revisions AS er_tmp
    WHERE er_tmp.approved = 1
    AND  e.id = er_tmp.event_id

为您的查询中的每一行“调用”。

(AD 2)正如@Dor告诉你的那样，你正在使用子查询中的alaias