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