Speed of FK vs non-FK

[sKunKbad]

I'm working on a project for a guy who designed his own database. Instead of having real foreign keys, he's got regular fields, and in many cases the names are not the same, for example "user_id" and "user_id_fk". I'm not a MySQL guru, and am under the impression that having real foreign keys means that the fields are automatically indexed by MySQL, resulting in normal performance. Am I right, and am I right that having these fake foreign keys is going to hurt performance unless we index all of these columns? Is indexing enough, or should I suggest making all of these real foreign keys?

[kicken]

Foreign keys are not automatically indexed. You still would have to add your own index to the column if desired. The benefit of having a real foreign key definition is integrity checking.

 

Also know that a real foreign key definition only exists if you use the InnoDB engine. MyISAM does not support foreign keys so their definitions are ignored when creating a table.

Edited January 28, 2014 by kicken

[requinix]

Foreign keys are not automatically indexed. You still would have to add your own index to the column if desired.

...unless you go for the full FOREIGN KEY (user_id_fk) REFERENCES table (user_id) (ie, actually tell MySQL of the foreign key relationship) in which case MySQL requires keys on both columns and will make them if you don't so yourself.

 

Using FOREIGN KEY Constraints

[kicken]

...unless

Aye I just found that and was coming back to edit my post. My initial search lead me to the InnoDB and Foreign Key constraints page which made no mention of an index except on the reference table.

[sKunKbad]

So it sounds like if integrity checking is not crucial, and we use InnoDB tables with indexing, we my not suffer any major performance issues. I am used to using real foreign keys with InnoDB tables. I need to read up on applying indexes manually.

[Barand]

Like all things it's a tradeoff.

 

InnoDB gives you FKs with enforced referential integrity and transactions.

MyISAM gives you extra speed on select statements and fulltext indexing.

 

And yes, if you use a field as FK with MyISAM then index it, and also index any fields regularly used in your WHERE clauses.