问题描述
更新:
只需在更明显的地方提及它即可.当我将IN更改为=时,查询执行时间从180降低为0.00008秒.可笑的速度差.
Just to mention it on a more visible place. When I changed IN for =, the query execution time went from 180 down to 0.00008 seconds. Ridiculous speed difference.
此SQL查询需要180秒才能完成!那怎么可能?有没有办法将其优化为更快?
This SQL query takes 180 seconds to finish! How is that possible? is there a way to optimize it to be faster?
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行,所以它应该不会太慢.
There are only about 5000 rows in each table so it shouldn't be so slow.
推荐答案
(将我的评论作为答案发布,显然确实有所作为!)
(Posting my comment as an answer as apparently it did make a difference!)
如果有人想对此做进一步调查,我刚刚进行了测试,发现它很容易复制.
If anyone wants to investigate this further I've just done a test and found it very easy to reproduce.
创建表格
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
)
)
等于解释输出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
)
)
在解释输出中http://img291.imageshack.us/img291/8129/52037513.png
这篇关于此SELECT查询需要180秒才能完成的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!