DELETE records from table having foreign key reference
668031Oct 30 2008 — edited Nov 12 2008Hi,
I'm with this problem:
There is a history table that has a column that should be a foreign key from a action table. But for performance issues it isn't a foreign key.
The size of the history table is really large and the action table is tiny.
The history table grows very fast, so I can't compromise how fast rows can be inserted into it (because of batch processing).
To remove a action it can take a long time, because it needs to test if the action had been used on the history table.
So I'm searching for a fast way to test if a action had been used in the history table.
First I suggested to create a index to the table that would keep a uniq id of action for the table history, so it wouldn't grow as fast as the history but I've been told that wold compromise the insertion time. I couldn't check the information that is onerous . As I'm new to DB, is it possible?
Just a second I thought on create a new row into the table action a new column named isUsed, and every time a new history row is inserted a it's action is altered to set this to 1. Which option is better?
Is there a better solution?
Any help is really appreciated.
Thanks
Sampaio, Diogo