Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file?

If not, what would you see as the fastest way to perform such operation?

EDIT: Also which one of these 2 methods would you see as the most efficient:

Multiple deletes

DELETE FROM myTable WHERE mykey=1 LIMIT 1;
DELETE FROM myTable WHERE mykey=2 LIMIT 1;
...
DELETE FROM myTable WHERE mykey=300000 LIMIT 1;

Or Batch delete

DELETE FROM myTable WHERE mykey IN (1,2,...,10000);
DELETE FROM myTable WHERE mykey IN (10001,10002,...,20000);
...
DELETE FROM myTable WHERE mykey IN (...,...,300000);

Thanks!

share|improve this question

2 Answers 2

DELETE TECHNIQUE #1

If you have a massive delete of keys, you may want to resort to the opposite of doing a massive DELETE: Create the table with the remaining values.

Let's say you loaded a table from the CSV file called KeysToDelete with the field mykey

Here is how to perform the massive DELETE without using DELETE

CREATE TABLE mynewtable FROM mytable;
INSERT INTO mynewtable
SELECT A.* FROM mytable A
LEFT JOIN KeysToDelete B USING (mykey)
WHERE B.mykey IS NULL;
ALTER TABLE mytable RENAME myoldtable;
ALTER TABLE mynewtable RENAME mytable;

That's it.

DELETE TECHNIQUE #2

If you want to use DELETE and the table is not that big, you could do a DELETE JOIN as follows:

DELETE A.* FROM mytable A INNER JOIN KeysToDelete B USING (mykey);

Here are my earlier posts where I discuss these techniques:

Give it a Try !!!

share|improve this answer
    
He talks about millions rows, in both solution he will locks his table for a while... –  Maxime FOUILLEUL Dec 20 '13 at 18:46
    
I have two questions: 1) How many rows in the table you are deleting from? 2) How many rows are you deleting? –  RolandoMySQLDBA Dec 20 '13 at 19:18
    
Additional question: Is the table InnoDB or MyISAM ??? –  RolandoMySQLDBA Dec 20 '13 at 19:20
    
I was talking about deleting 30 millions entries into a 100 millions entries table, and Sadly it's MyISAM. –  Syffys Jan 31 at 23:18

I don't think there is a similar command, but if your keys are simple (not composed) you can do the trick.

I usually generate a DELETE script with an SELECT CONCAT statement

You have your "IDs" file :

root@test:/tmp# cat /tmp/id.out
1
2
34
56
2345
3
4

Create a temp table to store it in SQL:

CREATE TEMPORARY TABLE test.ids (id int PRIMARY KEY);

Load your IDs:

LOAD DATA INFILE '/tmp/id.out' INTO TABLE test.ids;

Then generate a delete.sql script with a SELECT CONCAT:

SELEC CONCAT("DELETE FROM myTable WHERE ID=", id," LIMIT 1;") FROM test.ids INTO OUTFILE "/tmp/delete.sql";

Now you have your delete script:

root@test:/tmp# cat "/tmp/delete.sql"
DELETE FROM myTable WHERE ID=1 LIMIT 1;
DELETE FROM myTable WHERE ID=2 LIMIT 1;
DELETE FROM myTable WHERE ID=3 LIMIT 1;
DELETE FROM myTable WHERE ID=4 LIMIT 1;
DELETE FROM myTable WHERE ID=34 LIMIT 1;
DELETE FROM myTable WHERE ID=56 LIMIT 1;
DELETE FROM myTable WHERE ID=2345 LIMIT 1;

Then you can run your DELETE scripts:

mysql> source /tmp/delete.sql
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

You can also generate this type of script with OS tools (awk or notepad++)

You can run this type of script safely on a production master, you DELETE rows from the PK and you limit with LIMIT 1 so you do not risk to have replication lags.

Best Regards.

Max.

share|improve this answer
    
I can directly generate a delete.sql file with awk, which is much more efficient than generating it from MySQL. My problem is with the actual execution of all these deletes which is very very very slow (When I say massive, I mean like 30 millions entries). With such deletes, it takes hours... –  Syffys Dec 20 '13 at 10:47
    
You're right :) usually my IDs are in MySQL so I don't import it. You don't have an other choice except TRUNCATE table (more effecient than DELETE without WHERE) and reinsert missing IDs but it depends of the percentage of datas you want to remove from your table –  Maxime FOUILLEUL Dec 20 '13 at 11:01
    
Note than the important is that do not overload your servers or block your slaves. If you run well designed DELETEs (with WHERE on PK and LIMIT by 1) you will probably not have load issue, but if you work with high volumetry i'm not shocking than this traetment will take "hours", you can split it by lot of few millions DELETEs. –  Maxime FOUILLEUL Dec 20 '13 at 11:13
    
Ok, so there is basicly no better solution :P... –  Syffys Dec 20 '13 at 15:03

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.