estimating how long a delete statement should take in mysql

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?

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...