这个SELECT查询需要180秒才能完成。

UPDATE:

更新:

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.

在一个更明显的地方提一下。当我更改for =时，查询执行时间从180秒下降到0.00008秒。可笑的速度差。

This SQL query takes 180 seconds to finish! How is that possible? is there a way to optimize it to be faster?

这个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
    )
  )

There are only about 5000 rows in each table so it shouldn't be so slow.

每个表中只有大约5000行，所以它不应该这么慢。

3 个解决方案

#1

(Posting my comment as an answer as apparently it did make a difference!)

(我把我的评论作为答案，显然它确实起到了作用!)

Any difference if you change the IN to =?

如果你把IN改成=有什么区别吗?

If anyone wants to investigate this further I've just done a test and found it very easy to reproduce.

如果有人想进一步研究，我刚刚做了一个测试，发现它很容易复制。

Create Table

创建表

CREATE TABLE `filler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
)

Create Procedure

创建过程

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

Populate Table

填充表

  call prc_filler(5000)

Query 1

查询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

= http://img689.imageshack.us/img689/5592/equals.png解释输出

Query 2 (same problem)

查询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

在解释输出http://img291.imageshack.us/img291/8129/52037513.png

#2

Here is a good explanation why = is better than IN

这里有一个很好的解释为什么=比IN更好

Mysql has problems with inner queries - not well using indexes (if at all).

Mysql在内部查询方面存在问题——使用索引(如果有的话)并不好。

Make sure you have indexes on all the fields in the join/where/order etc.
确保对join/where/order等中的所有字段都有索引。
get those Max and MIN values in a separate query (use stored procedure for this entire thing if you want to skip the multiple requests overhead Or just do a request with multiple queries.
在单独的查询中获取这些最大值和最小值(如果您想要跳过多个请求开销，或者只对多个查询执行一个请求，请对整个查询使用存储过程。

Anyway:

无论如何:

SELECT
         IdLawVersionValidFrom 
FROM 
         question_law_version 
    JOIN 
         question_law
      ON 
         question_law_version.IdQuestionLaw = question_law.IdQuestionLaw
WHERE 
         question_law.IdQuestion=236 
     AND 
         question_law.IdQuestionLaw>63

ORDER BY 
         IdQuestionLawVersion DESC, 
         question_law.IdQuestionLaw ASC
LIMIT 1

#3

You can use EXPLAIN to find out how is it possible for a query to execute so slow.

您可以使用EXPLAIN找到查询执行如此缓慢的原因。

MySQL does not really like nested subselects so probably what happens is that it goes and does sorts on disk to get min and max and fail to reuse results.

MySQL并不喜欢嵌套子选择，所以可能会发生的情况是，它在磁盘上进行排序以获取最小值和最大值，却不能重用结果。

Rewriting as joins would probably help it.

以join的形式重写可能会有帮助。

If just looking for a quick fix try:

如果只是寻找一个快速解决办法，试试:

SET @temp1 =     
  (
  SELECT MIN(IdQuestionLaw) 
  FROM question_law 
  WHERE IdQuestion = 236 AND IdQuestionLaw > 63
  )

SET @temp2 = 
  (
  SELECT MAX(IdQuestionLawVersion) 
  FROM question_law_version 
  WHERE IdQuestionLaw = @temp1
  )

SELECT IdLawVersionValidFrom 
FROM question_law_version 
WHERE IdQuestionLawVersion = @temp2

#1