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.

Harald

what is a clustered primary key index ? dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html Why are clustered primary key indexes better than normal btree indexes ? #4419999 – Magee

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.

Vania

if you need fulltext search you just create a myisam table and use it to index back into your nicely clustered innodb data - simple. – Magee

Thanks, great answer. Judging by my application, it looks like InnoDB it the best one to use. – Harald

Answer 2

Yes indeed you may use both in the same database, you may choose for each table separately.

Orb

Answer 3

In short, InnoDB is good if you are working on something that needs a reliable database that can handles a lot of INSERT and UPDATE instructions.

and, MyISAM is good if you needs a database that will mostly be taking a lot of read (SELECT) instructions rather than write (INSERT and UPDATES), considering its drawback on the table-lock thing.

you may want to check out;
Pros and Cons of InnoDB
Pros and Cons of MyISAM

Vector

Answer 4

You don't choose InnoDB or MyISAM on a database level, but instead on a table level. So within the one database you could have some tables running the InnoDB engine and some running MyISAM. As you pointed out, you could choose to use InnoDB on the tables that require transactions etc, and MyISAM where you need other features such as fulltext searching.

Dell