简单的MySQL查询需要很长时间才能计算 [英] Simple MySQL queries taking long time to compute
问题描述
我正在学习MySQL，但遇到了问题.
I am just learning MySQL and I have a problem.
有时，对于各种查询，MySQL开始以15％至20秒的100％CPU使用率进行计算，然后返回通常说的结果:
Sometimes for various queries MySQL starts calculating at 100% CPU usage for 15-20 seconds and than it returns the result normally saying:
查询耗时0.1780秒.
Query took 0.1780 sec.
它发生在非常简单的查询上.例如，此查询花费了0.36秒.
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)
表的列表(7000行)花费了0.001秒.
The listing of the table (7000 rows) took 0.001 seconds.
另一方面，当我只想将这两者结合时，MySQL就会发疯，开始计算30秒，然后最终返回:Query took 0.1800 sec)
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)
为什么会这样?
这是另一个例子.该查询大约需要2秒钟的时间，并报告0.5
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
另一方面，此查询需要15到20秒，CPU为100％，但报告为0.3秒. (唯一的区别是AND(....)
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
我正在使用phpMyAdmin和最新的XAMPP进行测试.
I am using phpMyAdmin and the latest XAMPP for testing.
更新:
错误的查询时间似乎与phpMyAdmin有关，在命令行上，我得到以下时间:
The wrong query times seem to be related to phpMyAdmin, on command line I get the following times:
- 第一个查询:MySQL: 0.36 s -PostgreSQL: 0.37 s
- 第二个查询:MySQL: 43 s -PostgreSQL: 0.42 s
- 第三个查询:MySQL: 4.86 s -PostgreSQL: 0.05 s
- 第四个查询:MySQL: 1分5秒-PostgreSQL: 15秒
- 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
所以我有一个答案，为什么查询时间报告错误(在phpMyAdmin或XAMPP中出错)，我对为什么这种类似的查询在运行时间上有如此大的差异感兴趣?
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?
更新2:
出于完整性考虑，我也使用PostgreSQL进行了测试
Just for completeness I did the testing with PostgreSQL too
推荐答案
您是否尝试过使用mysql cmd提示符测试查询?如果问题仍然存在，则问题可能与mysql有关，但如果问题得以解决，那么我认为您的phpmyadmin存在问题.因此，请告知我，使用mysql cmd提示符尝试查询后，您的问题是否仍然仍然存在.
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.
这篇关于简单的MySQL查询需要很长时间才能计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
