Should I use MyISAM or InnoDB Tables for my MySQL Database?

Always use InnoDB by default.

In MySQL 5.1 later, you should use InnoDB. In MySQL 5.1, you should enable the InnoDB plugin. In MySQL 5.5, the InnoDB plugin is enabled by default so just use it.

The advice years ago was that MyISAM was faster in many scenarios. But that is no longer true if you use a current version of MySQL.

There may be some exotic corner cases where MyISAM performs marginally better for certain workloads (e.g. table-scans, or high-volume INSERT-only work), but the default choice should be InnoDB unless you can prove you have a case that MyISAM does better.

Advantages of InnoDB besides the support for transactions and foreign keys that is usually mentioned include:

• InnoDB is more resistant to table corruption than MyISAM.
• Row-level locking. In MyISAM, readers block writers and vice-versa.
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• MyISAM is stagnant; all future development will be in InnoDB.

See also my answer to MyISAM versus InnoDB

MyISAM won't enable you to do mysql level check. For instance if you want to update the imgId on both tables as a single transaction:

START TRANSACTION;UPDATE primary_images SET imgId=2 WHERE imgId=1;UPDATE secondary_images SET imgId=2 WHERE imgId=1;COMMIT;

Another drawback is integrity check, using InnoDB you can do some error check like to avoid duplicated values in the field UNIQUE KEY imgDate (imgDate). Trust me, this really come at hand and is way less error prone. In my opinion MyISAM is for playing around while some more serious work should rely on InnoDB.

Hope it helps

A few things to consider :

1. Do you need transaction support?
2. Will you be using foreign keys?
3. Will there be a lot of writes on a table?

If answer to any of these questions is "yes", then you should definitely use InnoDB. Otherwise, you should answer the following questions :

1. How big are your tables?
2. How many rows do they contain?
3. What is the load on your database engine?
4. What kind of queries you expect to run?

Unless your tables are very large and you expect large load on your database, either one works just fine.

I would prefer MyISAM because it scales pretty well for a wide range of data-sizes and loads.