Why is DELETE so much slower than SELECT, then DELETE by id?

I have a fairly busy InnoDB table (200,000 rows, I guess something like tens of queries per second). Due to a bug I got 14 rows with (the same) invalid email addresses in them and wanted to delete them.

I simply tried DELETE FROM table WHERE email='invalid address' and got "Lock wait timeout exceeded" after about 50 seconds. This is not terribly surprising, since the row column is not indexed.

However, I then did SELECT id FROM table WHERE email='invalid address' and that took 1.25 seconds. Running DELETE FROM table WHERE id in (...), copy-pasting the ids from the SELECT result, took 0.02 seconds.

What is going on? Can someone explain why the DELETE with the condition is so slow that it times out, but doing SELECT and then deleting by id is so fast?

Thanks.

EDIT: By request, I posted the table structure as well as some explain results. I should also note that there are no foreign keys referring to this table.

However, the situation seems straightforward to me: I have an unindexed field that I'm selecting against. This requires scanning the whole table, but it's not terribly big. id is the primary key, so deleting by id is very quick, as it should be.

``
mysql> show create table ThreadNotification2 \G
1. row
Table: ThreadNotification2
Create Table: CREATE TABLE ThreadNotification2 (
id bigint(20) NOT NULL AUTO_INCREMENT,
alertId bigint(20) DEFAULT NULL,
day int(11) NOT NULL,
frequency int(11) DEFAULT NULL,
hour int(11) NOT NULL,
email varchar(255) DEFAULT NULL,
highlightedTitle longtext,
newReplies bit(1) NOT NULL,
numReplies int(11) NOT NULL,
postUrl longtext,
sendTime datetime DEFAULT NULL,
sent bit(1) NOT NULL,
snippet longtext,
label_id bigint(20) DEFAULT NULL,
organization_id bigint(20) DEFAULT NULL,
threadEntity_hash varchar(255) DEFAULT NULL,
user_uid bigint(20) DEFAULT NULL,
PRIMARY KEY (id),
KEY FK3991E

If the field email is unindexed, then both DELETE and SELECT should work equally slow.

The only possibility I can think of is: You say that the table is heavily accessed. Maybe someone else ran a very long transaction (involving directly or indirectly those specific rows) while you were trying to execute the DELETE.

I think maybe you should insert some mock rows there and try to delete them. Do that 2 or 3 times. If there is a big difference in the duration of the DELETE, then the DB load is probably the reason.

PS: Do that only if people won't be annoyed by those mock rows :D .

StackExchange Database Administrators Q#21449, answer score: 7