Mysql – MyIsam engine transaction support

If you want to use phpMyAdmin to set up relations, you have to do 2 things. First of all, you have to define an index on the foreign key column in the referring table (so foo_bar.foo_id, in your case). Then, go to relation view (in the referring table) and select the referred column (so in your case foo.id) and the on update and on delete actions.

I think foreign keys are useful if you have multiple tables linked to one another, in particular, your delete scripts will become very short if you set the referencing options correctly.

EDIT: Make sure both of the tables have the InnoDB engine selected.

These days, I always default to using InnoDB, especially on the write-intensive tables you mention where MyISAM suffers from full table locking. Here's a to-the-point comparison.

Reasons to use MyISAM:

• Tables are really fast for select-heavy loads
• Table level locks limit their scalability for write intensive multi-user environments.
• Smallest disk space consumption
• Fulltext index
• Merged and compressed tables.

Reasons to use InnoDB:

• ACID transactions
• Row level locking
• Consistent reads – allows you to reach excellent read write concurrency.
• Primary key clustering – gives excellent performance in some cases.
• Foreign key support.
• Both index and data pages can be cached.
• Automatic crash recovery – in case MySQL shutdown was unclean InnoDB tables will still - recover to the consistent state- No check / repair like MyISAM may require.
• All updates have to pass through transactional engine in InnoDB, which often decreases - performance compared to non-transactional storage engines.

The above was taken from this site, which no longer seems to be working.