472,133 Members | 1,169 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,133 software developers and data experts.

thinking of adding foreign keys worth the while to turn to INNODB?

200 100+
Up until now didn't care for the storage engine and left the default MyISAM (thought there must be a reason for making it the default), I always used through my php scripts columns with the same data in different tables in order to connect them but now since lots of my sites' section are going into my MSc thesis I am wondering if it would be wrong not to actually declare them as foreign keys and add the restrictions, and if it is such a big deal it is easy to change from one type to another? Simply change the type and everything will work like before?

P.S. We are talking about tables with small to moderate size (do not thing anyone will get above 100 records maybe only the users' and products' table), and the select are much more common than the insert/update statements
Oct 29 '09 #1
1 2018
5,058 Expert 4TB

There are a lot of things to consider when deciding between MyISAM and InnoDB.
A few highlights:
  • InnoDB is transaction safe, MyISAM is not.
  • InnoDB supports Foreign Key constraints., MyISAM does not.
  • InnoDB supports row-level locking, while MyISAM supports only table-level.
  • MyISAM supports full-text searching, InnoDB does not.
See the InnoDB and MyISAM pages in the manual for full details.

As to performance, old rumors usually claim that MyISAM is faster than InnoDB, but using the current versions of MySQL, I am not so sure this is true. At least not for typical web-application databases. (See this article for details.)

My perception is that MyISAM is faster for small tables with fixed-length fields (CHAR, BINARY), while InnoDB is faster for large tables that use variable-length fields (VARCHAR, VARBINARY).

MyISAM is historically the default engine for MySQL, but today that is not really true. The Windows Essential Installer, for instance, defaults to InnoDB where it is available. A lot of Linux packages also default to InnoDB.
Oct 31 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Andrew Kuebler | last post: by
2 posts views Thread by Jeff Silverman | last post: by
2 posts views Thread by Morten | last post: by
2 posts views Thread by Ian Davies | last post: by
5 posts views Thread by clops | last post: by
2 posts views Thread by kal stevens | last post: by
5 posts views Thread by coosa | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.