We use MyISAM, InnoDB and Memory table types, depending upon the requirements of each table.
Each table type has its own benefits. For example, MyISAM is held back by table-level locking on writes whereas InnoDB only has to lock the particular row that is being written. However, if you can avoid locking, MyISAM is hugelyfaster for data writes than InnoDB. So it's not as simple as saying that one particular table type is better than another.

Well-known member

Basically when it comes down to it... MyISAM is better for WRITING, InnoDB is better for READING.

For tables that get written to often, such as session tables, it would be better for MyISAM. But tables are are just storages of information to be parsed later (like 99% of the tables out there), InnoDB is probably better.

That being said, MyISAM also supports full-text searching, while InnoDB does not.

XenForo Developer

That's true, but misses some important points, the most important of which is support for table-level or row-level locking.

While MyISAM is faster for writing, this comes at the expense of locking the entire table while write operations are happening. This is never a problem during development, when there are rarely going to be more than a couple of people concurrently connecting to the database, but in full-scale production when there are potentially thousands, it manifests itself as a serious lack of scalability.

InnoDB, while slower, is hugely more scaleable and reliable because of its support for transactions (the ability to roll-back changes in a multi-step operation) and row-level locking, so that during write operations only the affected rows are locked rather than the entire table, which allows multiple operations to be executed simultaneously on the same table.

On balance, InnoDB is a superior solution, and should be the default choice of table type unless there are specific reasons to require another type, such as wanting to use MySQL Full Text searching (requires MyISAM) or extremely fast non-permanent data throughput (could use a HEAP / MEMORY table).

Well-known member

Active member

I know this is an ancient thread, but wanted to add for anyone who stumbles across via Google.

If you don't know what version of MySQL you're running, just ignore--this isn't for you.

MySQL 5.6 and MariaDB 10 support fulltext indexes on InnoDB tables, so if you're running one of those, you can also convert xf_search_index to InnoDB. The one thing you'll need to change is in the Admin Control Panel > Performance, you'll need to uncheck use delayed insert. I know it's implied it's a good thing to check, but it's not supported on InnoDB tables. It's basically a hack to get MyISAM to do what InnoDB does natively, and if you try to leave it on for InnoDB tables, your users won't be able to post to the forum anymore (found out the hard way).

I switched the search index table to InnoDB on a forum that has >2M posts, and was shocked by how much faster the search index rebuild went compared to when it was MyISAM. I did a little researching, and basically on large tables InnoDB has a few characteristics that make for much faster inserts. It's also probably affected by me having a small MyISAM cache but a large InnoDB buffer pool. I'm also caching all my sessions to Memcached, so I went ahead and just switched all the tables to InnoDB. That lets MySQL handle figuring out what should be cached without me having to pick and choose whether to push more RAM toward MyISAM or InnoDB.