Choosing between MyISAM & InnoDB - two servers with related tables

Question

I don't want to make a mistake.

I am about to put my website online and I admit I haven't thought about which MySQL engine to choose: MyISAM or InnoDB.

I read here and there that InnoDB should be preferred now. MySQL is version 5.1.63.

Context: Two MySQL servers, serv1 and serv2.

• serv1: one table with unique id. Call this table daddy.
• serv2: one table child on which id's are the same as daddy's.

I have to update (and only this) my fields contained in daddy with the fields of child, where id's are the same. Indexes are on the columns that are important for the query.

So, to max performance out, should I stick with MyISAM or convert to InnoDB? I can still do it as the database is empty.

In my case, every hundredth of a second is worth a million $ :D (pure joke of course)

May you enlighten me?

Answer 1

"MyISAM is faster than InnoDB" -- This is an old wives tale. It used to be somewhat correct; now it is somewhat incorrect.

SELECTing during UPDATEing -- MyISAM is usually fast enough so this is not an issue, but InnoDB will usually not block at all. Go with InnoDB.

Power failure -- InnoDB automatically recovers.

You are talking about two "servers" -- Do you mean two different computers? And two differen instances of mysqld? There is nothing to have them talk to each other. (OK there are Replication and Federation, but it did not sound like you are into those.)

If you really meant "database" or "table" on a single MySQL instance, then so state. Tables (even in different databases) can be JOINed for most operations.

Answer 2

Have you considered upgrading from 5.1 while your database is empty?

You don't declare any good reasons that force you to use innodb (since you won't be using referential integrity between the two databases, etc).

MyISAM is typically known for being faster than innodb.

I would guess that you'll find other reasons to prefer innodb, like row-level locking (since you'll be doing lots of deletes). And if you ever decide to put these two, related tables in the same database, you can add the foreign key.

Hope that helps.