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

[mikek12004]

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

[Atli]

Hey.

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.