Thanks for the assistance! > Seems hard to answer without asking more questions, but some > basic questions first: > > Is complex_id indexed in both tables? (SHOW INDEX FROM aptreviews)
Yes, they are: mysql> SHOW INDEX FROM aptreviews; +------------+------------+----------+--------------+-------------+--------- --+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +------------+------------+----------+--------------+-------------+--------- --+-------------+----------+--------+---------+ | aptreviews | 0 | PRIMARY | 1 | review_id | A | NULL | NULL | NULL | | | aptreviews | 0 | PRIMARY | 2 | complex_id | A | 15272 | NULL | NULL | | +------------+------------+----------+--------------+-------------+--------- --+-------------+----------+--------+---------+ 2 rows in set (0.00 sec) mysql> SHOW INDEX FROM aptcomplexes; +--------------+------------+----------+--------------+-------------+------- ----+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +--------------+------------+----------+--------------+-------------+------- ----+-------------+----------+--------+---------+ | aptcomplexes | 0 | PRIMARY | 1 | complex_id | A | 35395 | NULL | NULL | | +--------------+------------+----------+--------------+-------------+------- ----+-------------+----------+--------+---------+ 1 row in set (0.00 sec) > Have you tried EXPLAIN'ing the query to see if maybe the index > isn't being used? mysql> EXPLAIN select count(*) from aptreviews, aptcomplexes where aptreviews.complex_id = aptcomplexes.complex_id; +--------------+--------+---------------+---------+---------+--------------- --------+-------+-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+--------+---------------+---------+---------+--------------- --------+-------+-------------+ | aptreviews | index | NULL | PRIMARY | 12 | NULL | 15272 | Using index | | aptcomplexes | eq_ref | PRIMARY | PRIMARY | 8 | aptreviews.complex_id | 1 | Using index | +--------------+--------+---------------+---------+---------+--------------- --------+-------+-------------+ 2 rows in set (0.00 sec) > 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. Ahhh... that helps. They are probably doing this from a JDBC connection (java object). I'll explore the possibility that something is wrong with their code as well. > 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. Yes, I agree. Doing a "top" has shown only a couple mysql processes at the top. Doing a "mysqladmin processlist" shows only a couple queries in an active state. Yet this morning everything completely came to a halt with a load of 44! However, I have seen some other queries in this state, but I don't have them written down. I'll watch for them again. > What's the output of ``mysqladmin status''? Uptime: 174824 Threads: 61 Questions: 2981844 Slow queries: 288 Opens: 7143 Flush tables: 1 Open tables: 64 Queries per second avg: 17.056 Thanks for the help! Since doing the query in the mysql client is so fast, I'm going to concentrate on a problem with the java connection to it for now. Unless what I've sent above raises any concerns for anyone on the list. Tauren > 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
