Delete large amount of rows from MySQL table is very slow

Question

My TABLE contains about 6 columns and about 10 million rows. There is one column that has about 5 million rows with the word "M-0" and the remaining 5 million with the word "M-1". I tried indexing this column and then deleting the rows which had the word "M-1". However this is taking very long. Is there a way to do this faster.

The name of the column is Distance. The code of the table is below:

CREATE TABLE MI_STAT_CONS_DHO (
  `Event` TEXT,
  `Event Description` TEXT,
  `Validated` CHAR(1),
  `Distance` CHAR(3),
  `Supplier Hub Group` TINYTEXT,
  `Special Project Flag` TINYTEXT
);

CREATE INDEX MI_STAT_CONS_DHO_DISTANCE_INDEX
ON MI_STAT_CONS_DHO (`Distance`);

DELETE FROM MI_STAT_CONS_DHO WHERE `Distance` = 'M-1';

Answer 1

If you have this much data which is needed to be deleted

I suggest you to:

1. create new temporary table with the data which will stay.
2. Truncate your main table
3. Move data from temporary table to your main table

or

1. create new temporary table with the data which will stay.
2. Drop your main table
3. Rename your Temp table as main table (dont forget to create constraints)

---

CREATE TABLE TMP_MI_STAT_CONS_DHO
   SELECT * FROM MI_STAT_CONS_DHO WHERE `Distance` = 'M-0';

TRUNCATE TABLE MI_STAT_CONS_DHO ;

INSERT INTO MI_STAT_CONS_DHO 
  SELECT * FROM MI_STAT_CONS_DHO;

DROP TABLE TMP_MI_STAT_CONS_DHO;