Delete Mysql data

Asked

Viewed 265 times

0

I have a table with a large amount of data (380 million). I need to erase the old data that is no longer useful in the system. I am generating backup for precaution.

What’s the best way to erase?

  • Delete with a restricting delete from which date ?
  • Delete little by little 50k in 50k ?
  • Delete bit by bit 1k in 1k ?
  • Or some other way ?

I’m doing 50k to 50k but it’s pretty time-consuming. What is the best solution for agility?

mysql
  • 1

    If you’re going to clear the table, I believe the best option would be a DROP, then recreate it. But let’s see the teachers' opinion! rs +1

  • Actually I’m not gonna clean up, just erase old records

  • In your table you have the data field ? post the photo here of the fields

  • 1

    Excluding large volumes of data

  • I would use the first option

  • @Exactly what I am doing, but somehow the delay is significant. I would like it to be more agile

  • 1

    @Pedroaugusto Here it goes from your scenario. Example: Let’s assume that "copy" the record is 1.5x of the "delete" time. So if of the 380 million records you need to delete "half", then it really compensates the script with the loop. Assuming, you need to delete 90%, then you could create an equal table, migrate the 10% of records you want to keep, drop this table, and change the name of the copied.

  • 1

    Maybe it’s faster to create a temporary table with the data you want to keep. Drop the old table, recreate it and re-insert the data from the time table,

  • 1

    @Reginaldorigo Basically what I said, but without wasting time getting back the data. But of course, it depends on the proportion that it needs to maintain/delete...

  • Opened my head that commented. I will apply. Obg

  • If you use the option to save the data in a time table you do not need to drop and recreate, you can truncate the table that in thesis is the same thing.

  • I recommend to delete per day, and be careful not to fill the transaction log

Show 7 more comments

1 answer

-1

  • Carlos, doesn’t the event block the table? Give a simple example of the event so we can see. Highlight the main details. So the answer is complete!

  • Rest assured that the event does not lock in the table; it’s just a background run! Let’s imagine that you have a table with sales data from 20 years ago called "tb_sistema_invoice" with the columns: id, customer name, total value and date of sale. Create an index for the sales date to help execute the "delete ... Where" command: CREATE INDEX idx_system_invoice ON schema.idx_system_invoice(sale date) USING BTREE.

  • With the index created... now you create an event to make the deletion: CREATE EVENT DELETION ON SCHEDULE AT '2018-08-24 00:00:00' DO delete from schema.idx_sistema_invoice Where sale date < '2010-01-01'. So you can do.

  • Po guy, nice these details... It would give you a very well formulated answer, with details, links to larger queries, etc.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.