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.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `analyses` ADD INDEX `analyses_idx_id_time_log_id` (`ID`,`time`,`log_id`);
SELECT 1 FROM analyses a1 INNER JOIN analyses a2 WHERE a1.log_id > a2.log_id AND a1.ID = a2.ID AND a1.time = a2.time