Geek Answers Handbook

Slow delete request for self write

Is this easier than this query?

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

Yes, apparently this is so ... because the performance on the above query is really bad when the matches table is very large.

matches - about 220 million entries. I hope this DELETE query takes about 15,000 records. How to improve query performance? I have indexes on both columns. UID and MatchedUID are the only two columns in this InnoDB table, both are of unsigned INT type (10). The request has been running for more than 14 hours on my laptop (i7 processor).

+1
performance sql join sql-delete self-join
source share
3 answers

Deleting as many records may take some time, I think it is as fast as possible, if you do so. If you do not want to invest in faster equipment, I propose a different approach:

If you really want to delete 220 million records so that only 15,000 records remain in the table, this is about 99.999% of all records. Why not

  • Create a new table,
  • just insert all the records you want to survive,
  • and replace the old with the new?

Something like this might work a little 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 that, you just need to check and create the necessary indexes, which should be quite fast, if only to deal with 15,000 records.

+7
source share

running explain select a. * from matches internal join match b ON (a.uid = b. matcheduid) explains how your indices are present and used

0
source share

I could set myself up for fries here, but when I perform a delete operation like this, due to self-connecting, isn; t should the query recount the join index after each deletion?

Although this is a clumsy and brute force, you can consider either:

but. Create a temporary table to store the uid as a result of the inner join, then join THAT, THEN change the delete.

OR

B. Add a boolean (bit) typed column, use a join for each match (this operation must be FAST), and THEN uses:

 DELETE * FROM matches WHERE YourBitFlagColumn = True

Then remove the boolean column.

0
source share

More articles:


All Articles