Fastest way to delete matching rows in MySQL?

Question

I have more than 10 million records in a table. I want to delete the records where the email column contains .ac..

What is the fastest method to perform the task?

Table Schema

CREATE TABLE `master` (
 `id` int(11) NOT NULL AUTO_INCREMENT, 
 `email` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Delete Query

DELETE FROM `master` WHERE ( email LIKE "%.ac.%" );

• Mannoj: show create table table_name; ?
• deepu: @MichaelGardner : the question has been updated.

Answer 1

You have not given much of a table description. Notwithstanding, I will try to answer your question.

For this example, let's use this table:

CREATE TABLE mytable
(
 id INT NOT NULL AUTO_INCREMENT,
 email VARCHAR(255),
 PRIMARY KEY (id)
);

First, try counting all the IDs that have .ac. in the email column.

SELECT COUNT(1) INTO @CompleteCount FROM mytable;
SELECT COUNT(1) INTO @DeleteCount FROM mytable WHERE LOCATE('.ac.',email) > 0;

There are two approaches to doing a DELETE:

APPROACH #1 : DELETE JOIN

If @DeleteCount < 5% of @CompleteCount, use a list of IDs to DELETE from mytable:

CREATE TABLE mytable_delete_id
SELECT id FROM mytable WHERE LOCATE('.ac.',email) > 0;
ALTER TABLE mytable_delete_id ADD PRIMARY KEY (id);
DELETE B.* FROM mytable_delete_id A INNER JOIN mytable B USING (id);
DROP TABLE mytable_delete_id;

This helps take the guess work out of which rows to DELETE by using a SELECT to get the IDs. The JOIN takes care of the DELETE.

APPROACH #2 : CREATE A NEW TABLE

If you have the following conditions:

• @DeleteCount> 30% of @CompleteCount
• your table does not have foreign keys/constraints

you could just create a new table and filter out the rows with .ac. in the email column:

CREATE TABLE mytable_new LIKE mytable;
#insert rows which does not contain '.ac.' from mytable
INSERT INTO mytable_new SELECT * FROM mytable WHERE LOCATE('.ac.',email) = 0;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

EPILOGUE

You can do either of these methods without checking table counta ahead of time. They will both work. If you are concerned that .ac. is in a huge number of rows, go with APPROACH #2. If there are not that many occurrences, go with APPROACH #1.

Answer 2

Did you try the simple method, i.e:

delete col_name from table table name where like condition ; 

I'm not sure about MySQL, but most probably it works in Oracle.

• ypercubeᵀᴹ: You need not the col_name in DELETE statements.