mysql 更新时计算,简单的MySQL查询需要很长时间才能计算

I am just learning MySQL and I have a problem.

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

Query took 0.1780 sec.

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

(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.

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)

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

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 ( .... )

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.

Update:

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

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

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

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

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

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?

Update 2:

Just for completeness I did the testing with PostgreSQL too

解决方案

Have you tried testing your queries with mysql cmd prompt ??? If the problem still persists then the issue might have been with mysql but if the problem is solved then i think you have a problem with phpmyadmin. So let me know that whether your problem still persists after trying your queries with mysql cmd prompt.