estimating how long a delete statement should take in mysql

Question

DELETE table2
FROM table2 INNER JOIN table1
   ON table2.CITY = table1.CITY
WHERE table1.COUNTRY = 'Russia'

both tables have about half a million records, about 10 columns each. each column is not larger than about 100 characters

how long will this take if there are about 250,000 records matching the criteria?

Answer 1

Depending on your index configuration (if you have a lot of indexes, it will slow the query down A LOT), you MAY be faster doing:

CREATE TABLE tmp_table2 LIKE table2;
ALTER TABLE tmp_table2 DISABLE KEYS;
INSERT INTO tmp_table2 SELECT t2.* FROM table2 AS t2 JOIN table1 AS t1 ON t1.CITY = t2.CITY WHERE t1.country != 'Russia';
ALTER TABLE tmp_table2 ENABLE KEYS;
DROP TABLE table2;
RENAME TABLE tmp_table2 TO table2;

Basically, it creates a new table, tells it not to update indexes, inserts the "good" records, tells it to update the indexes, and then renames it...

NOTE: Don't try to wrap this in a transaction, it won't work due to the DDL...