MySQL supports multiple storage engines, amongst which MyISAM and InnoDB are the most widely used. Each has its own advantages and there is no final rule on which is best. Oftentimes, using a mix of both will provide the best balance between data integrity and performance.

Comparison

MyISAM provides no data integrity and no transactions however it is generally faster when performing only SELECTs and INSERTs. In case of UPDATEs though, there may be a serious performance degradation because the table will be locked entirely (no row level locking).

One noticeable drawback of MyISAM is "crashed tables". This can happen in case of a server crash and this will require the admin to manually launch a full repair/rebuilt of the table and its indexes.

InnoDB if fully ACID compliant, supports transactions, foreign keys and row level locking.

With InnoDB, crashed tables are automatically repaired by replaying the transaction log.

All this creates some overhead in processing which may make them a little slower on small websites. But on websites with a little traffic, using row-level locking instea dof table locking actually makes the whole system much faster for concurrent accesses.

In b2evolution

In the case of b2evolution, we want to take full advantage of the performance and reliability benefits of a modern DBMS. InnoDB is required to properly execute transactions, which is necessary to preserve coherence of the data. Additionally, InnoDB allows better performance on sites with heavy traffic (row locking), allows better maintenance of the DB (referential integrity) and also better stability (no corrupted tables).

In b2evolution, we try to use InnoDB to guarantee data integrity whenever possible unless there is a dramatic impact on performance. Thus, b2evolution uses InnoDB for most of its tables but it also uses MyISAM in very specific cases, such as the hitlog, where testing has proved there was an advantage to stick to MyISAM.

At this point, testing on real life servers (via ) has shown that:

• There seems to be a significant impact on server performance when the hitlog is stored in InnoDB. Thus we keep this table as MyISAM as it is not critical if, in case of a crash, we lose some hitlog entries or there are some orphans not associated to a session any more (they will get purged eventually).
• The impact of storing the sessions table in InnoDB is not really noticeable. There seems to be no advantage of storing them as InnoDB though.

External references

• http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/
• http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB