Re: Foreign Keys Constraints, perforamance analysis

> > Deleting really showed what the MySQL team means. The deletion was
sometime=
> > s 30 seconds to < 1 second.
>
> Well, if I understand your rather vague description, you're comparing a
> simple bulk delete of all the tuples in the tables, versus a case where
> one table sees a bulk delete but the other ones see retail deletion (one
> tuple deleted per triggered query, and that tuple has to be searched for
> via an indexscan). Not surprising that it's much slower. The real
> question is what this scenario has to do with production activities.

It has nothing to do with production activities. I just want to know how,
and how much, Foreign Keys
Constraints affect performance.

I compare (1) manual deletion of person, married and child versus (2)
deletion of person which implies automatic deletion of married_fkc and
child_fkc using ON DELETE CASCADE.

> > If anyone could help, I would really appriciate if someone could tell me
wh=
> > y the child/child_fkc difference was so much more than the
married/married_=
> > fkc difference...
>
> That strikes me as odd too, since the one case has only one FK reference
> and the other has two ... seems like it should have been the other way
> 'round. Experimental noise maybe? Did you repeat the test to make sure
> the numbers were reproducible? Do you care to post all the details
> (scripts etc) so that others can try to reproduce it?

Well, the tests were run with quite high values and took quite some time, so
I doubt it was experimental noise.
And that was what I thought too, 2 FK versus 1.

> > I doubt is was becuase of the lack of VACUUM ANALYSE.
>
> You *should* be worried about that. The queries triggered by
> foreign-key checks are planned by the regular planner.

I'll rerun the test using VACUUM ANALYSE in between inserting into
married/married_fkc and child/child_fkc,
and post the results!

> regards, tom lane

This whole thing is about making myself aware of the performance impace of
Foreign Keys Constraints.