Re: Delete query takes exorbitant amount of time

Karim,

> Problem now is: this referencing table I expect to grow to about 110
> million rows in the next 2 months, then by 4 million rows per month
> thereafter. I expect that the time for recreating the foreign key will
> grow linearly with size.
>
> Is this just the kind of thing I need to watch out for? Any other
> suggestions for dealing with tables of this size? What can I do to my
> indexes to make them mo' betta?

How about getting some decent disk support? A single 10K SCSI disk is a bit
sub-par for a database with 100's of millions of records. Too bad you didn't
get a v40z ...

Beyond that, you'll want to do the same thing whenever you purge the
referencing table; drop keys, delete, re-create keys. Or think about why it
is you need to delete batches of records from this FKed table at all.

--
Josh Berkus
Aglio Database Solutions
San Francisco