I am currently designing the database schema for a high-load web application and I had some time to think on the famous MySQL optimization dilemma:
"Shall I use InnoDB, shall I user MyIsam, or shall I stay somewhere in between?".

[introduction]
As you may know MyIsam uses table-level locking and it performs best when there is a high number of selects but the data is not altered that much. On the other hand, InnoDB uses row-level locking; which allows selects and updates occur concurrently on a table.

Thus InnoDB may be faster, and is definitely more reliable, in a transactional enviroment where a lot of inserts /updates/deletes take place. InnoDB supports advanced features such as transactions, relational integrity and is fully ACID-compliant.
[/introduction]

First of all thanks God that it is most of the time possible to switch between the two table versions easily.

Here is a slow and dirty way:

1. dump your database (you backup before doing something risky, don't you?)
2. create a series of insert statements for your table data.
3. drop your table and relations on it.
4. recraete your table.
5. insert the data back.
6. create indexes and relations as necessary.

These steps work best when you cannot use

ALTER TABLE tblName CHANGE TYPE=[MyIsam|InnoDB]

command for one reason or another.

Otherwise you can re-create your table simply with the above single-line SQL.

...

So here is the heuristic I apply to choose between the two table types:

Q: Does your web application have a high load?
Does it have a high number of concurrent users at peak times?

If your answer is NO --> use MyIsam for all your tables.

Otherwise answer the following question:

Q: Is your application mission critical and you have
to preserve data integrity and sacrifice some speed
in order to keep the data in a consistent state?

If YES --> don't think for a second and go for InnoDB.

If NO it's better to use a hybrid setup of MyIsam and InnoDB tables:

So analyze your database structure for each table and ask the following questions for every single one of tables you have:

Q: Are there likely to be more selects on the table than
(insert/update/delete) operations?
[for instance a lookup table which is not changed
very often]

If YES --> use MyIsam.

Else answer the following question:

Q: Are there likely to be more inserts and fewer updates or deletes? (for instance if the users are building some sort of a category tree it is more probably that they will add new nodes than they will delete existing ones)

If YES-> use MyIsam.

If NO -> (updates and deletes will be relatively higher) use InnoDB.

...

Q: Are you still unsure?
Use MyIsam.

...

That's how I decide for the contents of my current db schema.

Hope that helps to someone out there as well.

 bu yaziyi sevdin mi?  hemen una ekle!