too many foreign keys

[davidklonski]

Hi guys

Could there be a problem in the design of a database schema which has a lot of foreign keys?
Also, if I define a foreign key between column A to column B, does the database index column A?

thanks

[r937]

question 1: define "a lot"

question 2: no

[davidklonski]

1) My question should be: Is there a disadvantage to using foreign keys (except for the storage they take)? Can they hurt performance?

2) If I set a column in a table as a primary key, does an index automatically gets generated for that column, or do I have to create an index for it explicitly?

thanks

[r937]

1) in myisam tables, there is neither advantage nor disadvantage, in innodb tables, there is only advantage, no disadvantage (well, except for a few cycles, but you want foreign keys turned on, otherwise you might as well use myisam tables)

2) primary key automatically creates a unique index (both myisam and innodb)

[davidklonski]

"primary key automatically creates a unique index (both myisam and innodb)"

do they also automatically create a regular index (for improving performance) ?

[r937]

a unique index is a regular index

[Betcour]

1) in myisam tables, there is neither advantage nor disadvantage

Well that's because myisam doesn't support foreign key

in innodb tables, there is only advantage, no disadvantage (well, except for a few cycles, but you want foreign keys turned on, otherwise you might as well use myisam tables)

Well there's a slowdown if you do lots of inserts, or if you update the column with the foreign key referencing another table. In this case, the database has to do a hidden SELECT everytime to make sure the foreign key is valid. That's why when populating a large table (ie restoring from a dump) with some foreign key constraints it can be interesting to temporary turn of the foreign key verification to speed up things.