Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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

share|improve this answer
    
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
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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