> > > So a db designer made a bloody mistake.
Not necessarily. If I'm never going to update or delete from the parent table
the index would be useless. I find very few of my foreign key relationships
actually need indexes on the child table. I usually only have the unique index
on the parent table.
And often the child table is the big table. The index would be very large and
have awful selectivity. The last thing I want is a 5-million record table with
half a dozen indexes each with 10-20 unique values.
> > > The problem is there's no easy way to find out what's missing.
> > > I'd really like EXPLAIN to display all subsequent triggered queries
> > > also, to see the full scans caused by missing indexes.
> >
> > I'd sure second that!
I think the root of problem here is the same as the root of the problem with
foreign key checks being slow for large batch updates and inserts. Namely that
foreign key constraint checks are being handled as a million small queries.
To handle foreign key constraints optimally they would really have to be
merged into the plan in a kind of join. For most inserts/updates something
like a nested-loop join that is effectively the same as the current triggers
would be used. But for large batch updates/inserts it's quite possible that it
would look more like a hash join or even a merge join.
To do that would probably mean throwing out the whole trigger-based
implementation though, which seems like an awfully big project.
And being able to disable and reenable constraints would still be nice.
They're never going to be instantaneous. And besides, speed isn't the only
reason to want to disable constraints temporarily. The database is a tool, it
should be there to do the DBA's bidding, not the other way around :)
--
greg