I'm running the following statement on MySQL 5.5 in a 1GB 1CPU SSD virtual machine from Linode:

DELETE table
    SELECT MAX(id) id, field
    FROM table
    GROUP BY field
) temp_table
    ON table.field = temp_table.field
WHERE table.id != temp_table.id

It's been stuck in the Sending data state for more than 24 hours, and now I know why: it has an awful execution plan:

mysql> EXPLAIN SELECT 1 FROM (SELECT MAX(id) id, field FROM table GROUP BY field) temp_table INNER JOIN table ON table.field = temp_table.field WHERE table.id != temp_table.id
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 381888 |                                 |
|  1 | PRIMARY     | users      | ALL  | NULL          | NULL | NULL    | NULL | 984873 | Using where; Using join buffer  |
|  2 | DERIVED     | users      | ALL  | NULL          | NULL | NULL    | NULL | 984873 | Using temporary; Using filesort |
3 rows in set (46.12 sec)

(MySQL 5.5 doesn't allow EXPLAIN DELETE, so I'm doing it with EXPLAIN SELECT 1 as per this answer)

Please note that only the EXPLAIN itself is taking 46 seconds to complete.

Should I stop this statement and try to do things better, or should I wait?

  • Yes, please stop it now. WARNING : It will take many hours to rollback. Jul 8 '15 at 16:46
  • Ok. I just stopped it. I did ^C on the liquibase update query I was using. It still shows as 'sending data' in the processlist. Is it doing rollback already, or do I need to KILL it in the processlist?
    – e18r
    Jul 8 '15 at 18:37
  • It may be rolling back. You must let it time out so it can cleanup the undo log. The ibdata1 file must have grown very much over the past 24 hours just to support the query. Jul 8 '15 at 18:38
  • emilio@brahms:~$ sudo ls -lh /var/lib/mysql/ibdata1 -rw-rw---- 1 mysql mysql 2.5G Jul 8 13:55 /var/lib/mysql/ibdata1
    – e18r
    Jul 8 '15 at 18:55
  • It got bigger! emilio@brahms:~$ sudo ls -lh /var/lib/mysql/ibdata1 -rw-rw---- 1 mysql mysql 2.6G Jul 9 12:15 /var/lib/mysql/ibdata1
    – e18r
    Jul 9 '15 at 17:15

I see what the query is doing. You are trying to DELETE a ton of rows and keep the last inserted id for every field.

I have a much better method.

DROP TABLE IF EXISTS keys_to_keep;
CREATE TABLE keys_to_keep
    id INT NOT NULL,
    PRIMARY KEY (id)
INSERT INTO keys_to_keep SELECT MAX(id) FROM mytable GROUP BY field;
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new
SELECT B.* FROM keys_to_keep A INNER JOIN mytable B USING (id);
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;

Check mytable. If it has only the last occurrence of field, then you can

DROP TABLE keys_to_keep;
DROP TABLE mytable_old;


I have recommended this technique before

I have also discussed doing soft deletes as an alternative (require maintaining an additional mapping table of ID marked deleted or an extra column to flag a row deleted):

  • Well, that seems like a very good way of doing it indeed. I'll give it a try as soon as I manage to stop and rollback the current statement. Thanks!
    – e18r
    Jul 8 '15 at 18:36

The EXPLAIN took so long because it evaluated the subquery. Perhaps the subquery took so long because of lack of INDEX(field, id).

When deleting a large chunk of a table, it is often faster to copy everything that you want to keep into a new table, then use RENAME to swap tables.

Or, you could do the deletes in chunks of 100-1000, walking through the PRIMARY KEY, if practical. More details in my delete blog.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.