REAL slow query

I've been having problems with queries like the one below. Can someone tell me what this does and if there is any way that I can keep them from stacking up and killing my server? They end up causing the post table to lock.

To the developers, how large of a board have you tested this application on? I ask because my smaller sites don't have any problems.

Code:

select count(*) as preposts,p1.postid,t.threadid, t.title
from thread t, post p1, post p2
LEFT JOIN deletionlog AS deletionlog ON(deletionlog.primaryid = p2.postid AND type = 'post')
where
p1.postid in (2763433,2763444,2763452,2763457,2763463,2764517,2764520,2764522,2772615,2772620,2772622,2772629)
AND deletionlog.primaryid IS NULL
AND p1.threadid=t.threadid
AND
(
p2.threadid=t.threadid
AND p2.visible=1
AND p2.dateline <= p1.dateline
)
group by p1.postid

Board Info:

• ~2.7M Posts
• ~350K Threads
• Full-text search enabled
• ~300 active users (900 second cookie timeout)

Server Info:

• (2) 2.8GHz Xeon (800MHz FSB)
• 2GB DDR2-400
• (3) 73GB 10K RPM SCSI Drives in RAID 5

Software Info:

• Red Hat Enterprise Linux 3
• MySQL 4.1.18 (upgraded from the RHEL3-supported 3.23.58 with the official MySQL-supplied RPMs )
• Apache 2.0.46-56 (official RHEL3 build)
• PHP 4.3.2-26 (official RHEL3 build)

My.cnf:

Code:

[mysqld]
safe-show-database
#low_priority_updates = 1
thread_concurrency = 4
#tmpdir = /mysqltmp     # On disk for larger operations
tmpdir = /tmp           # On tmpfs for smaller, faster operations
ft_min_word_len = 3
ft_max_word_len = 12
max_connections = 256
key_buffer = 128M
myisam_sort_buffer_size = 512M
join_buffer_size = 8M
read_buffer_size = 4M
sort_buffer_size = 8M
table_cache = 1800
thread_cache_size = 256
interactive_timeout = 60
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 128M
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
max_heap_table_size = 128M
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 8M
query_cache_size = 80M
query_cache_type = 1
query_prealloc_size = 163840
query_alloc_block_size = 32768
myisam_max_extra_sort_file_size = 10737418240
myisam_max_sort_file_size = 10737418240
myisam_repair_threads = 2

[mysqld_safe]
# nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
ft_min_word_len = 3
ft_max_word_len = 10
key_buffer = 128M
sort_buffer = 128M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

Yes indeed, this is a vBSEO query. However, it shouldn't be that slow.

Interesting... PM Sent.