MySQL Delete / Inner Join performance

Question

I have a MySQL database with ~27 million rows in one of its tables (analyses). I indexed two of the variables, time and ID, with the statements

ALTER TABLE `analyses` ADD INDEX(`time`);
ALTER TABLE `analyses` ADD INDEX(`ID`);

Each statement took ~4 minutes. I then attempted to delete redundant rows, with the statement

DELETE a1 FROM analyses a1 INNER JOIN analyses a2 WHERE a1.log_id > a2.log_id 
AND a1.ID= a2.ID AND a1.time=a2.time;

This statement has been executing for ~2 days without an error.

1. Is there a way I can estimate how long this statement should take?
2. From what I understand, interrupting this process (ctrl-C or kill) would not corrupt the database; am I correct that it'd be safe to do so?
3. Is there a way I could formulate the DELETE statement to increase execution speed, or should I just wait for this process to complete?

Answer 1

Well, just for fun, you could try creating a new table with just the records that you want to retain...

First get rid of your existing indexes, and instead create a compound index on (id,time).

Then do this...

CREATE TABLE my_new_table AS
SELECT x.* 
  FROM analyses x
  JOIN 
     ( SELECT id
            , time
            , MAX(log_id) log_id
         FROM analyses
        GROUP
           BY id
            , time
      ) y
     ON y.ID = x.ID 
    AND y.time = x.time
    AND y.log_id = x.log_id;