MySQL Forums
Forum List  »  InnoDB

optimizing delete from table with million of records
Posted by: Daniel Foobar
Date: October 06, 2013 04:26AM

Hi,
I have mainly three tables which refer each other like this:

Category | 1 ----> n | Package | 1 ----> n | File

All three tables are InnoDBs with foreign keys. Category has currently 186 records, Package 137,571 records and File 114,066,750 records. Update, Insert and Select works fine. Of couse I had to create several indexes espacally on the File table. I have also created a trigger which counts the rows of each table due to the lack of a row counter of InnoDBs. The trigger simply does an increment or decrement in another table for each insert or delete action.

Now I want to delete a few records and here comes my problem. At first I thought about removing one(!) record from the Package table which would also remove the corresponding records in the File table. But I canceled that query after 3 days. After that I digged a little more into the problem and tried to delete only one record from the File table. But that also tooked more than 3 days (I have also canceled the query). I guess this is because of recreating the indexes? To be honest I haven't thought about index types during the creation of the tables (meens I use the MySQL default types).

So here I am: Any ideas about speeding up the delete query without loosing the performance of the select, insert and update queries?

Thanks in advance
Daniel

Options: ReplyQuote


Subject
Views
Written By
Posted
optimizing delete from table with million of records
5883
October 06, 2013 04:26AM


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.