Greg Stark wrote:
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.
Apart from missing indices, this is certainly a problem. Statement level
triggers will solve this, as soon as they are fully implemented and
support OLD and NEW record sets.
Regards,
Andreas
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
