MySQL 从另一个查询中加入最近的记录很慢

Question

我有以下两个表:

CREATE TABLE `modlogs` (
  `mod` int(11) NOT NULL,
  `ip` varchar(39) CHARACTER SET ascii NOT NULL,
  `board` varchar(58) CHARACTER SET utf8 DEFAULT NULL,
  `time` int(11) NOT NULL,
  `text` text NOT NULL,
  KEY `time` (`time`),
  KEY `mod` (`mod`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4

CREATE TABLE `mods` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(30) NOT NULL,
  `password` char(64) CHARACTER SET ascii NOT NULL COMMENT 'SHA256',
  `salt` char(32) CHARACTER SET ascii NOT NULL,
  `type` smallint(2) NOT NULL,
  `boards` text CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`,`username`)
) ENGINE=MyISAM AUTO_INCREMENT=933 DEFAULT CHARSET=utf8mb4

我想加入带有 mod 名称的最新日志条目，但是我的查询非常慢(需要 5.23 秒):

SELECT *
FROM mods LEFT JOIN
     modlogs
     ON modlogs.mod = mods.id
     AND modlogs.time = (SELECT MAX(time)
                         FROM mods
                         WHERE mods.id = modlogs.mod
                        );

SO 上的所有其他答案似乎也使用依赖子查询。有没有一种方法可以更快地返回结果？

Answer 1

这里有另一种解决方案，将子查询放入派生表，避免了依赖子查询的问题。它只会运行一次子查询。

SELECT *
FROM mods AS m
LEFT JOIN (
    SELECT ml1.* 
    FROM modlogs AS ml1 
    JOIN (
        SELECT `mod`, MAX(time) AS time
        FROM modlogs 
        GROUP BY `mod`   
    ) AS ml2 USING (`mod`, time)
) AS ml ON m.id = ml.`mod`;