mysql 删除很慢_使用子查询在mysql基础上删除非常慢

这个

mysql查询运行了大约10个小时,但还没有完成.有些事情是可怕的错误.

两个表(文本和垃圾邮件)在这里.垃圾邮件将垃圾邮件的ID存储在我要删除的文本中.

DELETE FROM tname.text WHERE old_id IN (SELECT textid FROM spam);

垃圾邮件只有2列,都是整数. 800K条目的文件大小为几Mbs.两个int都是主键.

文本有3列. id(prim键),文本,标志.大约1200K条目,大约2.1千兆字节(大多数垃圾邮件).

服务器是一个至强四核,2千兆字节的ram(不要问我为什么).只有apache(为什么？)和mysqld正在运行.它是一个旧的免费bsd和mysql 4.1.2(不要问我为什么)

主题：6个问题：188805慢查询：318打开：810刷新表：1打开表：157每秒查询数：7.532

Mysql my.cnf：

[mysqld]

datadir=/usr/local/mysql

log-error=/usr/local/mysql/mysqld.err

pid-file=/usr/local/mysql/mysqld.pid

tmpdir=/var/tmp

innodb_data_home_dir =

innodb_log_files_in_group = 2

join_buffer_size=2M

key_buffer_size=32M

max_allowed_packet=1M

max_connections=800

myisam_sort_buffer_size=32M

query_cache_size=8M

read_buffer_size=2M

sort_buffer_size=2M

table_cache=256

skip-bdb

log-slow-queries = slow.log

long_query_time = 1

#skip-innodb

#default-table-type=innodb

innodb_data_file_path = /usr/local/mysql/ibdata1:10M:autoextend

innodb_log_group_home_dir = /usr/local/mysql/

innodb_buffer_pool_size = 128M

innodb_log_file_size = 16M

innodb_log_buffer_size = 8M

#innodb_flush_log_at_trx_commit=1

#innodb_additional_mem_pool_size=1M

#innodb_lock_wait_timeout=50

log-bin

server-id=201

[isamchk]

key_buffer_size=128M

read_buffer_size=128M

write_buffer_size=128M

sort_buffer_size=128M

[myisamchk]

key_buffer_size=128M[server:~] dmesg | grep memory

real memory = 2146828288 (2047 MB)

avail memory = 2095534080 (1998 MB)

read_buffer_size=128M

write_buffer_size=128M

sort_buffer_size=128M

tmpdir=/var/tmp

查询只使用一个cpu,top表示25％的cpu时间(所以4个中有1个).

real memory = 2146828288 (2047 MB)

avail memory = 2095534080 (1998 MB)

62 processes: 2 running, 60 sleeping

CPU states: 25.2% user, 0.0% nice, 1.6% system, 0.0% interrupt, 73.2% idle

Mem: 244M Active, 1430M Inact, 221M Wired, 75M Cache, 112M Buf, 31M Free

Swap: 4096M Total, 1996K Used, 4094M Free

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND

11536 mysql 27 20 0 239M 224M kserel 3 441:16 94.29% mysqld

知道怎么解决吗？