Slow self-join delete query

Does it get any simpler than this query?

delete a.* from matches a
    inner join matches b ON (a.uid = b.matcheduid)

Yes, apparently it does... because the performance on the above query is re开发者_开发技巧ally bad when the matches table is very large.

matches is about 220 million records. I am hoping that this DELETE query takes the size down to about 15,000 records. How can I improve the performance of the query? I have indexes on both columns. UID and MatchedUID are the only two columns in this InnoDB table, both are of type INT(10) unsigned. The query has been running for over 14 hours on my laptop (i7 processor).

Deleting so many records can take a while, I think this is as fast as it can get if you're doing it this way. If you don't want to invest into faster hardware, I suggest another approach:

If you really want to delete 220 million records, so that the table then has only 15.000 records left, thats about 99,999% of all entries. Why not

1. Create a new table,
2. just insert all the records you want to survive,
3. and replace your old one with the new one?

Something like this might work a little bit faster:

/* creating the new table */
CREATE TABLE matches_new
SELECT a.* FROM matches a
LEFT JOIN matches b ON (a.uid = b.matcheduid)
WHERE ISNULL (b.matcheduid)

/* renaming tables */
RENAME TABLE matches TO matches_old;
RENAME TABLE matches_new TO matches;

After this you just have to check and create your desired indexes, which should be rather fast if only dealing with 15.000 records.

running explain select a.* from matches a inner join matches b ON (a.uid = b. matcheduid) would explain how your indexes are present and being used

I might be setting myself up to be roasted here, but in performing a delete operation like this in the midst of a self-join, isn;t the query having to recompute the join index after each deletion?

While it is clunky and brute force, you might consider either:

A. Create a temp table to store the uid's resulting from the inner join, then join to THAT, THEN perfoorm the delete.

OR

B. Add a boolean (bit) typed column, use the join to flag each match (this operation should be FAST), and THEN use:

DELETE * FROM matches WHERE YourBitFlagColumn = True

Then delete the boolean column.

You probably need to batch your delete. You can do this with a recursive delete using a common table expression or just iterate it on some batch size.