What is the best approach to run a DELETE query on 92K of 4 million rows?

Question

Options that have occurred to me:

1. One big delete statement. For example:

   delete from x where (y = 'x' and x = 'y') or (y = 'a' and x = 'b')
   

2. Same as above, but chunk in smaller pieces, 10k at a time?

3. Create a stored procedure so all I'm sending are the the two query values.

Answer 1

92K out of 4M is still only 2.3% of your records. I'd suggest your first option, just throw what you want deleted at the database and let the database sort it out (if you give it to the database all at once, chances are it'll do it more efficiently than if you parcel it out). The only time I'd recommend chunking it is if your application is time-critical and you're worried about slowing it down (in which case the other solution is to do it off-hours).

If you were taking 20-30% or more of the records out of the table, I'd recommend dropping indexes, doing the delete, and recreating indexes, followed by an OPTIMIZE TABLE, but for 2% of the records in the table the index rebuild overhead isn't worth it (it's faster just to keep the indexes up-to-date while deleting the records).

That said, after you've done the delete, running OPTIMIZE TABLE might not be a bad idea - don't do that during peak hours though.

Answer 2

I would always use DELETE QUICK instead of just DELETE, as it does less work with the indexes during the delete, and then at a later stage when you get a chance to optimize the table, the index leaves that have been left behind get gathered up.

I'd certainly recommend reading the documentation on DELETE QUICK to see if it will help.

http://dev.mysql.com/doc/refman/5.5/en/delete.html

Answer 3

You want to make sure that your plan is Seeking to find the records of interest. So get it running nicely as a SELECT first with appropriate indexes.

If the locking involved in doing the DELETEs is a pain, then do it in batches. But if your query is scanning the table, then using batches will just end up in scanning the table multiple times.

Answer 4

You may want to attempt to gather the primary keys from the table and then delete them using a JOIN.

Example:

Give this table populated:

mysql> use test
Database changed
mysql> drop table if exists mytabletodeletefrom;
Query OK, 0 rows affected (0.00 sec)

mysql> create table mytabletodeletefrom
    -> (
    ->     id int not null auto_increment,
    ->     x char(1),
    ->     y char(1),
    ->     primary key (id)
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into mytabletodeletefrom (y,x) values
    -> ('a','b'),('b','b'),('a','a'),('a','b'),
    -> ('b','b'),('a','a'),('x','x'),('y','x'),
    -> ('y','y'),('x','x'),('y','x'),('y','y'),
    -> ('x','y'),('b','a'),('x','y'),('b','a');
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select * from mytabletodeletefrom;
+----+------+------+
| id | x    | y    |
+----+------+------+
|  1 | b    | a    |
|  2 | b    | b    |
|  3 | a    | a    |
|  4 | b    | a    |
|  5 | b    | b    |
|  6 | a    | a    |
|  7 | x    | x    |
|  8 | x    | y    |
|  9 | y    | y    |
| 10 | x    | x    |
| 11 | x    | y    |
| 12 | y    | y    |
| 13 | y    | x    |
| 14 | a    | b    |
| 15 | y    | x    |
| 16 | a    | b    |
+----+------+------+
16 rows in set (0.00 sec)

mysql> select * from mytabletodeletefrom
    -> where (y='x' and x='y') or (y='a' and x='b');
+----+------+------+
| id | x    | y    |
+----+------+------+
|  1 | b    | a    |
|  4 | b    | a    |
| 13 | y    | x    |
| 15 | y    | x    |
+----+------+------+
4 rows in set (0.00 sec)

Gather the keys you know will be be deleted into a deletekeys table

mysql> drop table if exists deletekeys;
Query OK, 0 rows affected (0.00 sec)

mysql> create table deletekeys like mytabletodeletefrom;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table deletekeys drop column x;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table deletekeys drop column y;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into deletekeys select id from mytabletodeletefrom
    -> where (y='x' and x='y') or (y='a' and x='b');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from deletekeys;
+----+
| id |
+----+
|  1 |
|  4 |
| 13 |
| 15 |
+----+
4 rows in set (0.00 sec)

Now, perform a JOIN on the DELETE with this query:

delete B.* from deletekeys A
left join mytabletodeletefrom B using (id);

Here is the result:

mysql> delete B.* from deletekeys A
    -> left join mytabletodeletefrom B using (id);
Query OK, 4 rows affected (0.00 sec)

mysql> select * from mytabletodeletefrom;
+----+------+------+
| id | x    | y    |
+----+------+------+
|  2 | b    | b    |
|  3 | a    | a    |
|  5 | b    | b    |
|  6 | a    | a    |
|  7 | x    | x    |
|  8 | x    | y    |
|  9 | y    | y    |
| 10 | x    | x    |
| 11 | x    | y    |
| 12 | y    | y    |
| 14 | a    | b    |
| 16 | a    | b    |
+----+------+------+
12 rows in set (0.00 sec)

In your case, the deletekeys table would contain the 92K of primary keys you know are to be deleted. Simply perform the left join of that table against the 4M rows. It should run fairly quick if the keys are already looked up. The time-consuming part would be gathering keys, especially if there is no compound index on y and x. Now, if the table to delete from has such an index:

create table mytabletodeletefrom
(
    id int not null auto_increment,
    x char(1),
    y char(1),
    primary key (id),
    key (y,x)
) engine=MyISAM;

then gathering keys would be quick as well.

Since you are deleting only 2.3% of the table, you could probably wait on optimizing the table. However, once you have delete a fair amount of rows, you have one of two options:

OPTION 1 : Just run OPTIMIZE TABLE

OPTIMIZE TABLE will not only compress the table but also run ANALYZE TABLE right after. ANALYZE TABLE compiles fresh index statistics for the benefit of the MySQL Query Optimizer. OPTIMIZE TABLE is great for InnoDB IF AND ONLY IF you have innodb_file_per_table configured. Otherwise, OPTIMIZE TABLE just makes the ibdata1 file grow bigger.

OPTION 2 : Perform your own OPTIMIZE TABLE and update index statistics at a later time

If your table is mainly for logging and/or archiving, you could simply compress the table without updating the index statistics. For example, if the table is MyISAM, simply convert it to MyISAM again:

alter table mytabletodeletefrom engine=MyISAM;

Later on, during off-peak hours, compile the statistics for the index (NOTE: This is not necessary if the table is InnoDB):

analyze table mytabletodeletefrom;

Give it a Try !!!