此SELECT查询需要180秒才能完成 [英] This SELECT query takes 180 seconds to finish

问题描述

更新:

只需在更明显的地方提及它即可.当我将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!)

如果您更改IN，则有任何区别 到=?

Any difference if you change the IN to =?

如果有人想对此做进一步调查，我刚刚进行了测试，发现它很容易复制.

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秒才能完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！