更新：
只是在更明显的地方提及它.当我更改IN为=时,查询执行时间从180下降到0.00008秒.可笑的速度差
这个SQL查询需要180秒才能完成！怎么可能？有没有办法优化它来更快？
SELECT IdLawVersionValidFrom
FROM question_law_version
WHERE IdQuestionLawVersion IN
(
SELECT MAX(IdQuestionLawVersion)
FROM question_law_version
WHERE IdQuestionLaw IN
(
SELECT MIN(IdQuestionLaw)
FROM question_law
WHERE IdQuestion=236 AND IdQuestionLaw>63
)
)
每张表中只有约5000行,所以不应该这么慢.
解决方法
(发表我的评论作为一个答案,显然这样做有所作为！)
Any difference if you change the
IN
to=?
如果有人想进一步调查,我刚刚做了一个测试,发现它很容易复制.
创建表
CREATE TABLE `filler` ( `id` int(11) NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`) )
创建过程
CREATE PROCEDURE `prc_filler`(cnt INT)
BEGIN
DECLARE _cnt INT;
SET _cnt = 1;
WHILE _cnt <= cnt DO
INSERT
INTO filler
SELECT _cnt;
SET _cnt = _cnt + 1;
END WHILE;
END
填充表
call prc_filler(5000)
查询1
SELECT id
FROM filler
WHERE id = (SELECT MAX(id) FROM filler WHERE id =
( SELECT MIN(id)
FROM filler
WHERE id between 2000 and 3000
)
)
Equals Explain Output http://img689.imageshack.us/img689/5592/equals.png
查询2(同样的问题)
SELECT id
FROM filler
WHERE id in (SELECT MAX(id) FROM filler WHERE id in
( SELECT MIN(id)
FROM filler
WHERE id between 2000 and 3000
)
)
In Explain Output http://img291.imageshack.us/img291/8129/52037513.png
