Delete big amount of data 2

[avihaimar]

Hey,

I need to delete big amount of data (around 300K).
I don’t want that the delete process will disturb to the main process (event record) to run.

So i wonder:
1. Is there an option to delete in chunks?
2. Is there a way to delete in background?
Can I check if the process or the db is busy and in case it is to wait with the delete?

Do you have any tips for this?

Thank you.

 

[TonyGroves]

300k bytes is only a small amount of data by MySQL standards. Regardless of the size, you don't have to worry about any disturbance; just issue your DELETE command and the MySQL server will make sure it doesn't interfere with any other processes. If you really wanted to, you could use DELETE LOW PRIORITY which will defer the deletion until a better time, or DELETE ... WHERE which will delete a specified subset of the records.

 

[avihaimar]

Sorry that i wasnt cleat.
i mean more that 300000 records .

so i have a big amount of records that i need to delete.

 

[TonyGroves]

Same advice applies.

 

[avihaimar]

What about divide the delete to chunks.Can i do it?
Is it good solution?

Thank yu a lot for your smart answer

 

[johnwm]

Look up the TOP keyword

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[TonyGroves]

avihaimar:

Sure, if you think it's necessary. However, that approach could leave the database in an inconsistent state - somebody could access the table while only some of the records have been deleted, but if that's acceptable, then you don't need to worry about it. It's also less efficient than the all-in-one-go approach, as the indexes need to be updated after each delete operation.

Unless the records are particularly big, or there are large numbers of indexes, deleting 300k records in one go is unlikely to take more than a few seconds.

 

[avihaimar]

Thank you.

Didnt understand.

You dont like the chunk idea?

I dont care about this data. i need to delete it in the background

 

[TonyGroves]

As I said there's nothing wrong with deleting in chunks; it's just more work. All the options have been discussed here, but you're the only person here who knows the details of your system, so it's your choice.

 

[r937]

johnwm, there is no TOP in mysql ;-)

avihaimar, tony likes the chunk idea, in fact he already mentioned it:

or DELETE ... WHERE which will delete a specified subset of the records

tony, nice explanation, star from me too



r937.com | rudy.ca

 

[avihaimar]

Thank you guys
Sorry for my English.


Lest say that i have Table Cat.(ID,NAME,IS_DELETED)
with 300K records.

i want to delete in low priority all the records that IS_DELETED is true, so how can i do it? ( do i have to do select count(*) from CAT where is-deleted = 'yes', in order to calculate how much chunk to run?

Can i examine the db performance in order to know if it is a good time for delete?

Thank you.
You are both greate.

Do you know if HQL support in those keywords

 

[johnwm]

Rudy,
Thanks - my bad! I did of course mean to say LIMIT

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[TonyGroves]

avihaimar:

You can use:
[tt]
DELETE LOW_PRIORITY
FROM cat
WHERE is_deleted='yes'
LIMIT 50000
[/tt]
The server will then report back the number of rows that were deleted; you could keep running the same query until 0 is reported. The LOW_PRIORITY will delay the delete until the read queue is empty.


Rudy:

Thanks!

 

[avihaimar]

Thank you.