论坛帖子表应该使用MyISAM还是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

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.