List:General Discussion« Previous MessageNext Message »
From:Tauren Mills Date:February 25 2002 11:24pm
Subject:CPU intensive query
View as plain text  
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

Thread
CPU intensive queryTauren Mills26 Feb
  • Re: CPU intensive queryMichael Bacarella26 Feb
    • RE: CPU intensive queryTauren Mills26 Feb
      • Re: CPU intensive queryShakeel Sorathia26 Feb