Can I use InnoDB and MyISAM tables in ONE database?

Question

Obviously both have their benefits. MyISAM is fast but may get currupted easily, InnoDB is slow but is more stable thanks to transactions and foreign keys. So it could be good to mix both engines in one database. If that's possible?

Sudoriferous

Answer 1

REMEMBER! It's OK to mix table types in the same database! In fact it's recommended and frequently required. However, it is important to note that if you are having performance issues when joining the two types, try converting one to the other and see if that fixes it. This issue does not happen often but it has been reported.

Taken from MySQL - InnoDB vs MyISAM

Adey

its ok to mix table types just as you said,. but mixing table types within transactions should be avoided,. – Vedetta

Answer 2

Sure, that's possible.

CREATE TABLE MyISAM_tbl(
    ....
)ENGINE=MyISAM;

CREATE TABLE InnoDB_tbl (
    ....
)ENGINE=InnoDB;

I do this all the time, because I prefer InnoDB for the FKs, but I sometimes need MyISAM for the full-text search. Never had a problem.

Bogtrotter

Answer 3

In a word yes. (CREATE TABLE lets you specify the engine type.)

However, you have to take care when you're querying across multiple table types. (For example you can't take advantage of foreign keys on MyISAM and you can't commit a transaction that affects MyISAM tables, etc.)

In essence, I suspect this might not be the best approach to take. (Unless you only need transactions for a defined set of tables that are segmented from the remainder of your database and InnoDB is too slow, getting a faster database server may well transpire to be less painful.)

Lexine