4

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?

2

"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.

  • Thanks, I chose Innodb on the go. Strange I cannot make this question answered :P See you !! And thanks again – Larry Feb 14 '13 at 12:17
  • @Larry click the green arrow under the answer? – jcolebrand Mar 16 '13 at 0:12
0

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.

  • It does Alain. Thanks. The only thing is that datas must be readable in front-end during the update (e.g : an intranet webserver). I can't afford to have my datas not accessible during the update; but I can afford to show the previous datas value during the operation). Inno fits better ? – Larry Nov 4 '12 at 9:35
  • With myisam, your table will be locked during writes. That's bad for read performance :) Sounds like innodb is the way to go. – Alain Collins Nov 4 '12 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.