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

Question

I have a table with 10 million records, which is the fastest way to delete and save the last 30 days.

I know that this can be done in the event planner, but I worry if it takes too long, it can lock the table for a long time.

It will be great if you can offer the best way.

Thank.

Answer 1

Sent, I would:

• Rename table
• Create an empty table with the same name as your original table
• Grab the last 30 days from the temp table and paste them back into the new table
• Drop temporary table

() 30 .

Answer 2

.

PARTITION BY LIST (TO_DAYS( date_field ))

1 , , :

ALTER TABLE tbl_name DROP PARTITION p#

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

Answer 3

, , , : merge, . partitioning, ( ) .

Answer 4

.

,

• , ,
• , .
• , .

: table - , newtable -

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

, . concurrency .

, , , sql temp delete. temp, , max_packet_size. MySQL 16 (16777216 ). temp max_packet_size. . , . , , .

Answer 5

Turn off your resource,, SELECT .. INTO OUTFILEoutput the parsing, delete the table, LOAD DATA LOCAL INFILE optimized_db.txt- it’s cheaper to recreate than UPDATE.