CODEWITHSUNDEEP
mysql
Sharpeye500
Sharpeye500

Reputation: 9073

Is there any way to do a bulk/faster delete in mysql?

I have a table with 10 million records, what is the fastest way to delete & retain last 30 days.

I know this can be done in event scheduler, but my worry is if takes too much time, it might lock the table for much time.

It will be great if you can suggest some optimum way.

Thanks.

Upvotes: 3

Views: 2224

Answers (5)

Hiram
Hiram

Reputation: 51

To expand on Michael Todd's answer.

If you have the space,

  1. Create a blank staging table similar to the table you want to reduce in size
  2. Fill the staging table with only the records you want to have in your destination table
  3. Do a double rename like the following

Assuming: table is the table name of the table you want to purge a large amount of data from newtable is the staging table name no other tables are called temptable

    rename table table to temptable, newtable to table;
    drop temptable;

This will be done in a single transaction, which will require an instantaneous schema lock. Most high concurrency applications won't notice the change.

Alternatively, if you don't have the space, and you have a long window to purge this data, you can use dynamic sql to insert the primary keys into a temp table, and join the temp table in a delete statement. When you insert into the temp table, be aware of what max_packet_size is. Most installations of MySQL use 16MB (16777216 bytes). Your insert command for the temp table should be under max_packet_size. This will not lock the table. You'll want to run optimize table to reclaim space for the rest of the engine to use. You probably won't be able to reclaim disk space, unless you were to shutdown the engine and move the data files.

Upvotes: 1

mluebke
mluebke

Reputation: 8809

You could try partition tables.

PARTITION BY LIST (TO_DAYS( date_field ))

This would give you 1 partition per day, and when you need to prune data you just:

ALTER TABLE tbl_name DROP PARTITION p#

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

Upvotes: 4

Wrikken
Wrikken

Reputation: 70490

Not that it helps you with your current problem, but if this is a regular occurance, you might want to look into a merge table: just add tables for different periods in time, and remove them from the merge table definition when no longer needed. Another option is partitioning, in which it is equally trivial to drop a (oldest) partition.

Upvotes: 1

Michael Todd
Michael Todd

Reputation: 17051

Offhand, I would:

  1. Rename the table
  2. Create an empty table with the same name as your original table
  3. Grab the last 30 days from your "temp" table and insert them back into the new table
  4. Drop the temp table

This will enable you to keep the table live through (almost) the entire process and get the past 30 days worth of data at your leisure.

Upvotes: 5

kagali-san
kagali-san

Reputation: 3082

Shutdown your resource, SELECT .. INTO OUTFILE, parse output, delete table, LOAD DATA LOCAL INFILE optimized_db.txt - more cheaper to re-create, than to UPDATE.

Upvotes: 0

Related Questions