Foreign Keys Constraints, perforamance analysis

I wanted to know how Foreign Keys Constraints affected the performance. The MySQL team has not implemented this feature yet as they state it has many disadvantages. Well, the only one I could see was speed. Still the software industry is more and more aiming towards maintainable, clean code... so I still prefer using it.

Anyway, I wanter to see in detail how much you loose when having it, so I created a few tests. I started by building two small hiearchies of tables (with the help of this mailinglist) that looks like this:

Person -> married -> child
Person -> married_fkc -> child_fkc

The *_fkc has foreign keys, and foreign keys constraints, while the other hasn't.

The tables look like this (no SERIAL due to the need to reset the sequence values to 0):

CREATE TABLE person (

 

       id         integer DEFAULT nextval('person_id_seq'),

       

       name        TEXT

 

);

 

CREATE UNIQUE INDEX person_id_key ON person(id);

 

CREATE TABLE married_fkc (

 

       id         integer DEFAULT nextval('married_fkc_id_seq'),

 

       person1ID     integer NOT NULL REFERENCES person ( id ) ON DELETE CASCADE,

       person2ID     integer NOT NULL REFERENCES person ( id ) ON DELETE CASCADE,           

 

       UNIQUE ( person1ID ),

       UNIQUE ( person2ID )

 

);

 

CREATE UNIQUE INDEX married_fkc_id_key ON married_fkc(id);

 

CREATE TABLE married (

 

       id         integer DEFAULT nextval('married_id_seq'),

 

       person1ID     integer NOT NULL,

       person2ID     integer NOT NULL,

       

       UNIQUE ( person1ID ),

       UNIQUE ( person2ID )

 

);

 

CREATE UNIQUE INDEX married_id_key ON married(id);

 

CREATE TABLE child_fkc (

 

       id         integer DEFAULT nextval('child_fkc_id_seq'),

       marriedID     integer NOT NULL REFERENCES married_fkc ( id ) ON DELETE CASCADE,

 

       name        TEXT

 

);

 

CREATE UNIQUE INDEX child_fkc_id_key ON child_fkc(id);

 

CREATE TABLE child (



       id            integer DEFAULT nextval('child_id_seq'),

       marriedID     integer NOT NULL,

 

       name          TEXT

       

);



1. First, with no measuring of time, I fill the person table with 2*N persons.

2. Filling the married tables with N tuples ( [1, 2] [3, 4] [5, 6] ... ). Measuring time.

3. Fillinf the child tables with 2*N tuples ( [1] [1] [2] [2] ... ) (two children per married couple). Measuring time.

4. Emptying the tables. This means, for *_fkc tables only delete person table. But for the other tables, manual deletion of all the tables.



(this is ofcourse run in two rounds, as step four deletes a common (to both sets) table)



I was not very surprised to see how little difference it made when inserting into the married_fkc table (< 3%), compared to inserting to the married table. I was VERY surprised to see that the difference when inserting into child and child_fkc gave more than 5 times a difference than inserting into the married and married_fkc (25% slower).



Deleting really showed what the MySQL team means. The deletion was sometimes 30 seconds to < 1 second.



If anyone could help, I would really appriciate if someone could tell me why the child/child_fkc difference was so much more than the married/married_fkc difference...



I doubt is was becuase of the lack of VACUUM ANALYSE. It was quite a big difference. Strange is that married_fkc has TWO foreign keys, while child_fkc has only ONE.



Thanks.



Daniel Åkerud