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 --------------------------------------------------------------------- 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