Re: Deleting vs foreign keys

On 10/25/05, WireSpot <wirespot(at)gmail(dot)com> wrote:
> Thanks for the tip, guys. I'll go punch in some indexes and I'll be
> back to report how much of a difference it made.

Adding indexes made the dropping of entries for the "master" table
roughly 6 (six) times faster. It's definitely an improvement and I
suspect it will benefit the application in many other ways.

It's still not perfect though. Based on the figures I've got, some of
the biggest entries in the "master" table would still take about 45
minutes to delete along with all their foreign key referrals. So as
far as practical purposes are concerned, I'm back to square one.

Fortunately, in this case, all the tables hold a redundant site ID.
The only practical alternative I see is to drop all the foreign keys,
delete from all the tables based on that redundant ID, then add the
keys back and hope I don't hit any conflicts.

This is still not perfect, because while I do this there cannot be any
regular access to the database (can't afford to while foreign keys are
down). But if the drop becomes blazing fast in this manner, it would
be worth it... I guess.

Any advice about what went wrong when this database was designed? Was
there any way that the designers could've kept the consistency offered
by foreign keys and at the same time allow for fast deletes?