Mysql delete and optimize very slow

Question

I searched Internet and Stack Overflow for my trouble, but couldn't find a good solution.

I have a table (MySql MyISAM) containing 300,000 rows (one column is blob field).

I must use:

DELETE FROM tablename WHERE id IN (1,4,7,88,568,.......)

There are nearly 30,000 id's in the IN syntax.

It takes nearly 1 hour. Also It does not make the .MYD file smaller although I delete 10% of it, so I run OPTIMIZE TABLE... command. It also lasts long...(I should use it, because disk space matters for me).

What's a way to improve performance when deleting the data as above and recover space? (Increasing buffer size? which one? or else?)

might be better off stuffing those IDs into a temporary table then do a delete/join query instead. — Marc B, Dec 20 '13 at 19:33
using in with a query basically forces a check against each of those values on each row that gets deleted. You're potentially scanning through 30,000 id's before the delete actually runs. — user2366842, Dec 20 '13 at 19:36
@MarcBs and @user2366842s comments are correct. Creating a temporary table that has all the IDs, then `delete from tablename where id in (select id from temporarytable)` might help the optimizer improve speed. Also, consider dropping indexes (except the one on id!) before the delete and rebuilding them after the delete might be faster than having the delete constantly change them. — Guntram Blohm, Dec 20 '13 at 19:41
another option (not sure how viable this is, depends on how many records you have total) is if the table doesn't have foreign key constraints, you could copy over everything that ISN'T in the the list to a temp table, run a truncate on the original table, and copy everything left over back (presuming this is a one off delete job, not something that runs on any sort of regular basis) — user2366842, Dec 20 '13 at 20:03

score 2 · Answer 1 · answered Dec 20 '13 at 21:19

With IN, MySQL will scan all the rows in the table and match the record against the IN clause. The list of IN predicates will be sorted, and all 300,000 rows in the database will get a binary search against 30,000 ids.

If you do this with JOIN on a temporary table (no indexes on a temp table), assuming id is indexed, the database will do 30,000 binary lookups on a 300,000 record index.

So, 300,000 binary searches against 30,000 records, or 30,000 binary searches against 300,000 records... which is faster? The second one is faster, by far.

Also, delaying the index rebuilding with DELETE QUICK will result in much faster deletes. All records will simply be marked deleted, both in the data file and in the index, and the index will not be rebuilt.

Then, to recover space and rebuild the indexes at a later time, run OPTIMIZE TABLE.

score 1 · Answer 2 · answered Dec 20 '13 at 19:36

1

I think the best approach to make it faster is to create a new table and insert into it the rows which you dont want to delete and then drop the original table and then you can copy the content from the table to the main table.

Something like this:

INSERT INTO NewTable SELECT * FROM My_Table WHERE ... ;

Then you can use RENAME TABLE to rename the copy to the original name

RENAME TABLE My_Table TO My_Table_old, NewTable TO My_Table ;

And then finally drop the original table

DROP TABLE My_Table_old;

answered Dec 20 '13 at 19:36

Rahul Tripathi

168,305
31
280
331

This would help to get a smaller file, but he would need space for both files while creating the new one, and he might just not have that space. – Guntram Blohm Dec 20 '13 at 19:37
OP is deleting only 10% of the records. I don't think this method would be faster. – Marcus Adams Dec 20 '13 at 21:20

score 1 · Accepted Answer · edited May 23 '17 at 12:07

The size of the list in your IN() statement may be the cause. You could add the IDs to a temporary table and join to do the deletes. Also, as you are using MyISAM you can use the DELETE QUICK option to avoid the index hit whilst deleting:

For MyISAM tables, if you use the QUICK keyword, the storage engine does not merge index leaves during delete, which may speed up some kinds of delete operations.

score 1 · Answer 4 · answered Dec 20 '13 at 20:18

try this

create a table name temptable with a single column id
insert into table 1,4,7,88,568,......
use delete join something like
DELETE ab, b FROM originaltable AS a INNER JOIN temptable AS b ON a.id= b.id where b.id is null;

its just an idea . the query is not tested . you can check the syntax on google.

Mysql delete and optimize very slow

4 Answers4