Should a forum posts table use MyISAM or InnoDB

Question

For a forum, should I use MyISAM or InnoDB for the table that stores the text?

I know that MyISAM supports full text searching, which sounds like it's what I should go for, but while reading, I've come across this very confusing sentence.

You should use InnoDB when transactions are important, such as for situations in which INSERTs and SELECTs are being interleaved, such as online message boards or forums.

Well, but for an "online message boards or forums" I would need good search to search through the text of each post, so doesn't it make more sense to use MyISAM? Can someone clarify what's most commonly used?

Answer 1

Apart from MySQL's native full text searching solution, which as you already identified requires the MyISAM storage engine, you may also want to consider the popular third-party search engines Sphinx and Apache Lucene, with which you can use InnoDB tables.

You could also stick to MySQL's solution, and use InnoDB storage for all tables except for one table which will simply hold the post_id and text_body. This is assuming that you only require full text searching for the content of forum posts.

In addition note that while the major deficiency of MyISAM is the lack of transaction support, there are other important issues as well for a production system. You may want to check the following article for further reading:

• MySQL Performance Blog: Using MyISAM in production

Answer 2

For any large volume of text I'd use InnoDB to store it and Sphinx to index it. It provides a lot more search features than what's built into mysql.

Answer 3

Generally speaking, for non-critical data, I'd use InnoDB tuned for speed. (E.g., I wouldn't flush binary logs on every commit.) I'd only use MyISAM for things like logging tables.

As others have mentioned, you can use external search indexes if you need to do full text searches. They are more powerful and faster than MySQL's.

Regarding the part about transactions, it's a bit of a lie. You could do the same thing with MyISAM by locking tables for WRITE. If you are inserting into, say, three tables when posting a message, you could lock all three tables for WRITE, and the data would look consistent (assuming all three INSERTS succeeded).

But the problem with that is you are locking the entire table, which can slow down concurrent usage. In fact, that is one of InnoDB's major advantages over MyISAM: it has row-level locking.

In short: if you are unable to set up an external indexer, and your site does not experience too much traffic, then MyISAM will work fine despite it being less robust. You can emulate the consistency of transactions with table locks. (I do not mean to make it sound like the table locking is somehow equivalent to transactions.)

Otherwise, I'd use InnoDB with an external indexing service.