Very slow delete on mysql base with subquery

This mysql query is running for around 10 hours and has not finished. Something is horribly wrong.

Two tables (text and spam) are here. Spam stores the ids of spam entrys in text that I want to delete.

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

spam has just 2 columns, both are ints. 800K entries has a file size of several Mbs. Both ints are primary keys.

text has 3 columns. id (prim key), text, flags. around 1200K entries, and around 2.1 gigabyte size (most spam).

The server is a xeon quad, 2 gigabyte ram (don't ask me why). Only apache (why?) and mysqld is running. Its an old free bsd and mysql 4.1.2 (don't ask me why)

Threads: 6 Questions: 188805 Slow queries: 318 Opens: 810 Flush tables: 1 Open tables: 157 Queries per second avg: 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_buffe开发者_JAVA技巧r_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

The query is using just one cpu, top says 25% cpu time (so 1 of 4).

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

Any idea how to fix it?

In my experience sub queries are often a cause of slow execution times in SQL statements, therefor I try to avoid them. Try this:

DELETE tname FROM tname INNER JOIN spam ON (tname.old_id = spam.textid);

Disclaimer: This query is not tested, make backups first! :-)

Your choice of where id in (select ...) will always perform poorly.

Instead, use a normal join which will be very efficient:

DELETE `text` 
FROM spam
join `text` on `text`.old_id = spam.textid;

Notice selection from spam first, then joining to text, which will give the best performance.

of corse it will take a lot of time because it execute the subquery for every record but by using INNER JOIN directly this query is executed only one time lets think that the query will take

10 ms for 50000 rec  full time = 50000 * 10 ms ---> 8.333 minutes !! at least don't forget the condition and deleting time .....

but using join the query will be executed only one time :

DELETE t FROM tname.text t INNER JOIN (SELECT textid FROM spam) sq on t.old_id = sq.textid ;

Copy rows that are not in spam form text to new table. Then delete text table and rename created table. Good idea is not to add any keys to created table. Add keys after renaming.

I think you might want to chunk the deletes down with a LIMIT and you might want to do that delete in a JOIN. I wrote a bit more about this in this article which helps specifically with archiving of data and deleting no longer needed rows.

https://shatteredsilicon.net/blog/2021/07/12/mariadb-mysql-performance-tuning-optimization-how-to-delete-faster-on-mysql/