optimizing delete from table with million of records
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
Subject
Views
Written By
Posted
optimizing delete from table with million of records
5883
October 06, 2013 04:26AM
2022
October 06, 2013 12:44PM
1677
October 06, 2013 02:02PM
1858
October 07, 2013 01:08PM
1976
October 07, 2013 09:30PM
1823
October 08, 2013 07:11AM
1591
October 08, 2013 10:23PM
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.