Re: Deciding between MyISAM and InnoDB

[James Day]

I'm assuming that your application resembles a hospital with many consultants and many patients attempting to book appointments. Potentially many patients trying to book each available time slot for each of the best consultants.

Faster may be MyISAM, but it's easy to try both ways, so do. Do feel free to change it in production as your loads change.

I'd use InnoDB as first choice because InnoDB is usually better at automatic recovery after crashes. There is automatic recovery for MyISAM but my experience was that it's not as effective of that in InnoDB and also became unacceptably slow for production web site use as database sizes grew past 10 gigabytes. InnoDB just became more reliable and offered greater availability after trouble, in practice. If you have tables which are updated only in batches every few days, those won't see much advantage from that aspect, so they might as well be MyISAM if you see any performance gain.

If you do consider InnoDB, take great care with your choice of primary/clustered index. That will be the physical order of records and you can see tremendous performance improvement from a good choice. Good being one which lets your most common query for ranges of records find many hits in the same page, increasing cache efficiency and decreasing disk reads. Or simply your typical set of queries. Say, all for the next or last two weeks in the same set of pages. MyISAM will effectively add in time order (time added), so you don't have the same opportunity to optimize seeks with careful design there.

If you're able to get a design where the frequently used and updated records can fit in RAM, you'll find yourself with an application which scales very well for high user and traffic levels.

If multiple update transactions end up affecting the same set of pages InnoDB may have a performance advantage over MyISAM, because InnoDB caches record writes better than MyISAM. Seems quite likely for your application.

InnoDB can sometimes be faster for selects because it'll automatically build internal hash indexes when it thinks those are of value. SHOW INNODB STATUS will tell you about memory being used for this.

Since you don't know your final traffic levels, design for a master-slave configuration and let the slave(s) be pointed to the master as well for single server setup. That way at high loads the slaves will be able to handle most reads while the master gets only the writes and few reads involved in an update transaction - things like confirming that a slot which was open when you asked the slaves wasn't used during the short delay between a master write using it and a slave getting that write. Much easier to do this at initial design and writing time.

Your PHP looping suggests that you don't have an index which can handle both the where condition and any order by you are using. If you can't do both efficiently with one index (avoiding range scans, for example), you might consider sorting in PHP and using an index optimal for your where condition. Connections and queries are very fast but it's still nice to try to find a way to avoid making seven times as many. If you still can't do it, consider having the PHP write a UNION to get all of the independent queries made with one query/response exchange. Avoid using persistent connections, though - you'll scale better that way.

When using InnoDB, avoid the autocommit mode if you're updating with multiple statements in one batch of updates. Autocommit will do one fsync per update statement and you'll benefit from dodging the extra fsyncs by wrapping them all in a transaction then getting just one fsync for them all.