I am hosting websites that use the mysql database. One of my customers has
a query that they run occasionally that really bogs down the entire server.
The load sometimes jumps up into the double digits (on a Red Hat 6.2
server).
Here is their query:
select count(*) from aptreviews, aptcomplexes where aptreviews.complex_id =
aptcomplexes.complex_id
When I execute this query in the mysql client, it returns instantaneously:
mysql> select count(*) from aptreviews, aptcomplexes where
aptreviews.complex_id = aptcomplexes.complex_id;
+----------+
| count(*) |
+----------+
| 15257 |
+----------+
1 row in set (0.18 sec)
However, when checking on long-running processes with "mysqladmin
processlist", this query doesn't go away for a long time:
| 18823 | webs | localhost.localdomain | webs_aptrate | Query | 1 |
Sending data | select count(*) from aptreviews, aptcomplexes where
aptreviews.complex_id = aptcomplexes.complex_id |
| 18867 | webs | localhost.localdomain | webs_aptrate | Query | 1 |
Sending data | select count(*) from aptreviews, aptcomplexes where
aptreviews.complex_id = aptcomplexes.complex_id |
The tables that are used are somewhat large:
mysql> select count(*) from aptreviews;
+----------+
| count(*) |
+----------+
| 15263 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from aptcomplexes;
+----------+
| count(*) |
+----------+
| 35395 |
+----------+
1 row in set (0.00 sec)
Any ideas what might be causing this?
Here's the version:
[root@s2 tauren]# mysql -V
mysql Ver 11.15 Distrib 3.23.40, for pc-linux-gnu (i686)
Thanks!
Tauren
