Quick question.
Talking to my friend with 25 years db experience he was telling me if you use foreign key constraints in a db; when a table is being written to for example, a table for messages, it will lock out the relative row on the parent table for say, users.
Is this true?
Also he said that applying indexes to the Foreign Key Columns should overcome this locking out, is this true?
I am concerned as my website traffic is growing and I can imagine this being an issue!
Thanks!
I wrote a blog post on this - http://www.mysqlperformanceblog.com/2010/09/20/instrumentation-and-the-cost-of-foreign-keys/ - the demo is just as your friend described. There is locking on parent rows!
Now for the part about explaining the difference between an "index" and a "key":
From the MySQL manual, "InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.". That is, if you don't add an index when you add your foreign key, InnoDB will automatically.
No amount of indexing prevents the parent locking described in my blog post.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With