Seems hard to answer without asking more questions, but some
basic questions first:
Is complex_id indexed in both tables? (SHOW INDEX FROM aptreviews)
Have you tried EXPLAIN'ing the query to see if maybe the index
isn't being used?
Questions:
That the queries are stuck on 'Sending data' seems to indicate that the
client isn't picking up the data, not necessarily that the server is
busy churning away at retrieving the count.
What other queries are running? It seems strange that just two queries
in the 'Sending data' state would take up double digit cpu load on
Linux.
What's the output of ``mysqladmin status''?
On Mon, Feb 25, 2002 at 03:24:25PM -0800, Tauren Mills wrote:
> 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)
--
Michael Bacarella | 545 Eighth Ave #401
| New York, NY 10018
Systems Analysis & Support | [EMAIL PROTECTED]
Managed Services | 212 946-1038
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
