MySQL Forums
Forum List  »  Newbie

delete timing-feasible?
Posted by: peter andrus
Date: February 10, 2009 10:27AM

i am struggling to figure out if i've hit a wall or not.

I have a DB with about 100 columns, and a LOT of rows. The DB in question has about 350 Million rows. It is a merge table with 36 different table, so about 10 mil per table. However, i cannot delete directly from the tables themselves because when i get the indication to delete, the info indicating what table to find it in has been lost... long story. Point is, i have to delete from the merge table itself.

However, i am getting HORRIBLE delete times right now. i tested on a 100K db. I was deleting about 10K rows every 30 seconds. That seems slow enough, but then i upped the row count to 20 Million. Now, it is taking me about 90-110 seconds to delete 10K. Once i get up to the the 350 Million row database, i'm deleting 10K claims in about 6.5 minutes. In these cases, i am using a PRIMARY KEY column to delete with.

Is this time drop-off completely expected and justified? i know the size will drop performance, but this seems excessive to me. Is there anything i can do to shave some time from this deletion process? i have months where i need to delete 5-10 Million rows at a time, which will take literally days. any help is appreciated!

Options: ReplyQuote

Written By
delete timing-feasible?
February 10, 2009 10:27AM
February 11, 2009 11:21PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.