mysql - MySQL Join性能真的很慢

我试图比较具有超过1,000,000行的同一张表的值。以下是我的查询，大约需要25秒才能获得结果。

EXPLAIN SELECT DISTINCT a.studyid,a.number,a.load_number,b.studyid,b.number,b.load_number FROM
(SELECT t1.*, buildnumber,platformid FROM t t1
INNER JOIN testlog t2 ON t1.`testid` = t2.`testid`
WHERE (buildnumber =1031719 AND platformid IN (SELECT platformid FROM platform WHERE platform.`Description` = "Windows 7 SP1"))
)AS a
JOIN
(SELECT t1.*,buildnumber,platformid FROM t t1
INNER JOIN testlog t2 ON t1.`testid` = t2.`testid`
WHERE (buildnumber =1030716 AND platformid IN (SELECT platformid FROM platform WHERE platform.`Description` = "Windows 7 SP1"))
)AS b
ON a.studyid=b.studyid AND a.load_number = b.load_number AND a.number = b.number

有人可以帮我改进此查询以获得足够快的结果吗？
这里的问题是，即使我有数字和load_number索引，查询也不使用它。我不知道为什么它总是被忽略。

谢谢。

最佳答案

首先，您有一个愚蠢的查询。您正在检索六列，但是只有三个值。查看on子句。

我认为您最好的选择是使用条件聚合重写查询。我认为以下是等效的：

SELECT t1.studyid, t1.load_number, t1.number
FROM t t1 INNER JOIN
     testlog t2
     ON t1.testid = t2.testid
WHERE t2.buildnumber IN (1031719, 1030716) AND
      platformid IN (SELECT platformid FROM platform p WHERE p.Description = 'Windows 7 SP1'))
GROUP BY studyid, load_number, number
HAVING MIN(buildnumber) <> MAX(buildnumber)

对于此查询，您需要在platform(Description, platformid)和testlog(buildnumber, platformid)和t(testid)上建立索引。

关于mysql - MySQL Join性能真的很慢，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30769643/

PlatformID

mysql - MySQL Join性能真的很慢