Very slow mysql based delete with subquery

Question

This mysql query runs for about 10 hours and is not completed. Something is terribly wrong.

There are two tables here (text and spam). Spam stores the spam identifiers in the text I want to delete.

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

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

the text has 3 columns. id (prim key), text, flags. about 1200 thousand records and about 2.1 gigabytes (most spam).

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

Topics: 6 Questions: 188805 Slow queries: 318 Opens: 810 Flash tables: 1 Open tables: 157 requests 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_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 

Only one processor is used in the request, the top one - 25% of the processor 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 this?

Answer 1

In my experience, helper queries often cause slow execution times in SQL statements, so I try to avoid them. Try the following:

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

Disclaimer: This request has not been tested, first back up !:-)

Answer 2

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

Instead, use a normal connection that will be very effective:

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

Deselect spam first, then join the text for the best performance.

Answer 3

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

Answer 4

it takes a long time for the cors, because it performs a subquery for each record, but with the help of INNER JOIN this request is executed only once, let's think that the request 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 the connection, the request will be executed only once:

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