InnoDB or MyISAM - Why not both?

Question

I've read various threads about which is better between InnoDB and MyISAM. It seems that the debates are to use or the other. Is it not possible to use both, depending on the table?

What would be the disadvantages in doing this? As far as I can tell, the engine can be set during the CREATE TABLE command. Therefore, certain tables which are often read can be set to MyISAM, but tables that need transaction support can use InnoDB.

Answer 1

You can have both MyISAM and InnoDB tables in the same database. What you'll find though, when having very large tables is, MyISAM would cause table-lock issues. What this ultimately does is, it locks the whole table for a single transaction, which is very bad if you have many users using your website. e.g If you have a user searching for something on your site and the query takes minutes to complete, no other users could use your site during that period because the whole table is locked.

InnoDB on the other hand uses row-level locking, meaning, that row is the only row locked from the table during a transaction. InnoDB can be slower at searches because it doesn't offer full text search like MyISAM, but that isn't a big problem when you compare it to table-level locking of MyISAM. If you use InnoDB, like many large sites, then you could use a server side search engine like Sphinx for full text searches. Or you could even use a MyISAM table to do the searching like f00 suggested. I'd personally recommended InnoDB mainly because of the row-level locking feature, but also because you can implement full text searching in many other ways.

Basically, if you have a message board application with lots of selects, inserts as well as updates, InnoDB is probably the generally appropriate choice. But if you're not building something like that (or any other thing with registered users) and your working mostly with static content (or more reads than writes), then you could use MyISAM.