Deciding between MyISAM and InnoDB

[David Niry]

Hello,

after reading many posts about the use of InnoDB vs. MyISAM, I still am undecided as to which one to pick for a new online service my company is lauching.

Without revealing too much due to non-disclosure constraints, my application deals with reservations using a calendar with 1 week at a time (7 days displayed on the page at a time), each day broken down down into 1-hour segments.

The calendar will display existing reservations and allow members to place reservations at available time slots.

It is estimated there there will be, on average, about 15-20 SELECT operations for every INSERT. No UPDATES are expected on the RESERVATIONS table being read, but the RESERVATIONS table will be joined to the MEMBERS table on the member_id key during the SELECT, and the MEMBERS table will occasionally get UPDATE statements if a member updates his profile.

As I understand it from posts, MyISAM should provide faster performance for SELECT operations. However, I also read in a number of places that because of MyISAM's table level locking vs. InnoDB's row level locking, InnoDB may actually be faster under heavy load and when many simultaneous users are doing INSERT statements, since MyISAM will prevent the entire table from being read while InnoDB will only lock the necessary row.

The application is expected to be used by a fairly large number of concurrent users as the business and our client list grows. Market study for end of year 1 allows us to expect up to 10,000 users a day browsing through 2-3 weeks of the calendar and placing a reservation (resulting in the aforementioned estimate of 15-20 SELECTS for one INSERT. Tests have shown that, in order to display existing reservations on the calendar, it was faster to do 7 smaller queries, 1 for each day of the week and loop once over it in PHP for each day of the week, rather than do one larger query for the whole week and loop over it in PHP 7 times, once for each day of the week).

Transactions are not an issue here, so there is no need for InnoDB in that sense... But my question is: given the high expected number of concurrent users and the ratio of SELECT vs. INSERT, what do you think will provide better performance once heavy loads are reached ?

Many thanks on any advice anyone may have....