Mysql – ny reason not to use foreign keys in MySQL

foreign keylockingMySQLtransaction

I've never really used foreign keys before but I am building a project where they could come in handy. I have looked at some other large software such as some forum systems and they don't use foreign keys, so I'm wondering why wouldn't they!?

I understand that it would come in handy for situations where they have the username in the users table and then on the posts table for example they have the users username in the author field rather than an id (my assumption is they do this to avoid expensive joins to the users table); so for this situation you would think adding a foreign key to the posts table to reference the username would be a good idea.

I'm wondering – when you do something such as this:

// Start transaction
SELECT * FROM users WHERE username='some_username' LIMIT FOR UPDATE;
// End transaction

Because of the foreign key placed on the posts table with reference to the username in the users table, does that then cause not only that row in the users table to be locked but also all rows in the posts table that reference the users username to be locked as well?

If not, are there any other reasons why one would not use foreign keys for instances such as this?

Best Answer

Because of the foreign key placed on the posts table with reference to the username in the users table, does that then cause not only that row in the users table to be locked but also all rows in the posts table that reference the users username to be locked as well?

If you are concerns about transaction (insert, update, delete) Locking will occur when Mysql is trying to update some rows. In your situation, chances are you'll either update the user table OR the post table. The fastest updates or deletes are done using primary keys. PK in innodb are clustered (physically ordered) so it is very fast. If you were to update a post for a user, you would first grab the user_id (select user_id from user where username =?). You would then update the post later (update post set .. WHERe userid = x). I would avoid the select for Update. I would encourage you to use innodb for transactional application. Not only it supports FK, but support row locking and point in time recovery (to name a few).

If you system will be 90% select, few updates, or data warehouse style, then I would recommend Myisam.

Related Question