简单的MySQL查询花费很长时间来计算-SimpleMySQLqueriestakinglongtimetocompute

I am just learning MySQL and I have a problem.

我只是在学习MySQL而且我遇到了问题。

Sometimes for various queries MySQL starts calculating at 100% CPU usage for 15-20 seconds and than it returns the result normally saying:

有时,对于各种查询,MySQL开始以100%的CPU使用率计算15-20秒而不是通常会返回结果:

Query took 0.1780 sec.

查询耗时0.1780秒。

It happens on very simple queries. For example this query took 0.36 seconds.

它发生在非常简单的查询上。例如,此查询花了0.36秒。

(SELECT DISTINCT a1.actor 
 FROM   actors AS a1, 
        actors AS a2 
 WHERE  a1.title = a2.title 
        AND a1.YEAR = a2.YEAR 
        AND a1.actor = a2.actor 
        AND a1.character_name <> a2.character_name) 

The listing of the table (7000 rows) took 0.001 seconds.

该表(7000行)的列表耗时0.001秒。

On the other hand when I just want to combine these two, MySQL goes crazy and starts calculating for 30 seconds and then finally returning: Query took 0.1800 sec)

另一方面,当我只想将这两者结合起来时,MySQL会疯狂并开始计算30秒然后最终返回:查询耗时0.1800秒)

SELECT actor 
FROM   actors 
WHERE  actor NOT IN (SELECT DISTINCT a1.actor 
                     FROM   actors AS a1, 
                            actors AS a2 
                     WHERE  a1.title = a2.title 
                            AND a1.YEAR = a2.YEAR 
                            AND a1.actor = a2.actor 
                            AND a1.character_name <> a2.character_name) 

Why is this happening?

为什么会这样?

Here is an other example. This query takes around 2 second and reports 0.5

这是另一个例子。此查询大约需要2秒钟并报告0.5

SELECT DISTINCT a1.character_name 
FROM   (actors AS a1 
        NATURAL JOIN movies AS m1), 
       (actors AS a2 
        NATURAL JOIN movies AS m2) 
WHERE  a1.character_name = a2.character_name 
       AND ( m1.title <> m2.title 
              OR ( m1.title = m2.title 
                   AND m1.year <> m2.year ) ) 
       AND m1.country <> m2.country 

On the other hand this query takes 15-20 seconds, CPU 100% but reports 0.3 seconds. (The only difference is a bracket after AND ( .... )

另一方面,此查询需要15-20秒,CPU 100%,但报告0.3秒。 (唯一的区别是AND后的括号(....)

SELECT DISTINCT a1.character_name 
FROM   (actors AS a1 
        NATURAL JOIN movies AS m1), 
       (actors AS a2 
        NATURAL JOIN movies AS m2) 
WHERE  a1.character_name = a2.character_name 
       AND m1.title <> m2.title 
        OR ( m1.title = m2.title 
             AND m1.YEAR <> m2.YEAR ) 
           AND m1.country <> m2.country 

I am using phpMyAdmin and the latest XAMPP for testing.

我正在使用phpMyAdmin和最新的XAMPP进行测试。

Update:

The wrong query times seem to be related to phpMyAdmin, on command line I get the following times:

错误的查询时间似乎与phpMyAdmin有关,在命令行上我得到以下时间:

• 1st query: MySQL: 0.36 s - PostgreSQL: 0.37 s

第一个查询:MySQL:0.36秒 - PostgreSQL:0.37秒

• 2nd query: MySQL: 43 s - PostgreSQL: 0.42 s

第二个查询:MySQL:43秒 - PostgreSQL:0.42秒

• 3rd query: MySQL: 4.86 s - PostgreSQL: 0.05 s

第三个查询:MySQL:4.86秒 - PostgreSQL:0.05秒

• 4th query: MySQL: 1 min 5 s - PostgreSQL: 15 seconds

第4次查询:MySQL:1分5秒 - PostgreSQL:15秒

So I have the answer for why were the query times reported wrongly (bug either in phpMyAdmin or XAMPP), I am interested in why do such similar queries have such a big difference in running time?

所以我有答案为什么错误地报告查询时间(phpMyAdmin或XAMPP中的错误),我感兴趣的是为什么这样的类似查询在运行时间上有这么大的差异?

Update 2:

Just for completeness I did the testing with PostgreSQL too

为了完整起见,我也使用PostgreSQL进行了测试

4 个解决方案

SELECT DISTINCT a1.actor 
FROM   actors AS a1, 
actors AS a2 
WHERE  a1.title = a2.title 
AND a1.YEAR = a2.YEAR 
AND a1.actor = a2.actor 
AND a1.character_name <> a2.character_name) 

SELECT *
FROM actors a1
JOIN actors a2 ON (a1.title = a2.title AND a1.actor = a2.actor)
GROUP BY a1.actor
HAVING a1.character_name <> a2.character_name

SELECT actor 
FROM   actors 
WHERE  actor NOT IN (SELECT DISTINCT a1.actor 
                     FROM   actors AS a1, 
                            actors AS a2 
                     WHERE  a1.title = a2.title 
                            AND a1.YEAR = a2.YEAR 
                            AND a1.actor = a2.actor 
                            AND a1.character_name <> a2.character_name)

select   actor
from     actors
group by actor, year, title
having   count(character_name) = 1