Jump to content

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/285728-speed-of-fk-vs-non-fk/
Share on other sites

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 by kicken

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

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.

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.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.