MySQL different storage engines for tables

Question

Is it a good practice to set up a database with some InnoDB and some MyISAM tables depending on their use? I would set InnoDB only for those involved in transactions and MyISAM for everything else. Does this have some kind of negative effect or anything to avoid this solution?

Meet

Any reason not to use InnoDB for all of them? – Mosstrooper

Why would you want to mix? Is the reason for MyISAM FULLTEXT indexing? If so thats available with innodb from MySQL 5.6 and higher. – Misconception

@KayNelson, have you tried using InnoDB fulltext in 5.6? It needs some more improvement, IMHO. – Giant

@BillKarwin , yes ive been using it for a while and i find it to be as good as MyIsam except that updates on columns with FT is faster with MyISAM. I have nightly updates in my current system and i drop the indexes and do the updates, then readd the indexes which has proven to be the best way for me. But i must also say, i have not done extensive testing to compare. But i have nothing but good to say about FT in innodb. – Misconception

@KayNelson, wow cool I'm glad it's working for you. I find it weird that you have to have a primary key column FTS_DOC_ID (in all caps) and that fulltext searches give kind of unexpected results. – Giant

@BillKarwin so am I :),but, the primary key column i also find wierd. I havent gotten unexpected results from my search, but i always make use of IN BOOLEAN MODE. Maybe thats why? – Misconception

@KayNelson, you might be interested in a three-part blog written by one of my colleagues at Percona, with a deep test drive of 5.6 fulltext indexes: mysqlperformanceblog.com/2013/02/26/… – Giant

@BillKarwin that is great, im very intereseted and will for sure read it. Thanks! – Misconception

Answer 1

Use InnoDB for all tables, unless you can prove a compelling performance advantage for using MyISAM, or you need MyISAM's FULLTEXT or GIS indexes.

InnoDB is the default storage engine in MySQL 5.5 and later. InnoDB is crash-safe, and it is being developed actively. And it has better performance than MyISAM in most cases.

MyISAM gets corrupted easily during a crash. It does not save data to disk synchronously (relies on filesystem buffering). It has only table-level locking. It is receiving no development or enhancement, and it's on its way to becoming deprecated.

Giant

@RolandoMySQLDBA, yeah, but MyISAM is still used for system tables, and it will probably not become truly deprecated until 5.8 (IMHO). – Giant

Perhaps, MySQL (eh Oracle) could make a Storage Engine called SYSTEM which maintains the MyISAM systems tables. Then, there's those 5 pesky InnoDB system tables, the CSV logs. Ugh !!! – Commitment

@RolandoMySQLDBA, nice thought, but honestly I think they will try to find a way to make the system tables InnoDB. – Giant

I suppose that would be wiser because you could just copy the MyISAM tables to another installation and possibly get credentials you shouldn't have elsewhere. On the flipside, if the system tables are InnoDB (which 5 are now) you would have to remember to mysqldump the system tables on some occasions. Either way, Oracle will have something up its sleeve soon enough. – Commitment

Update... It won't be 5.8; it is 8.0; early releases came out late in 2016. The "Data Dictionary" is now in InnoDB tables, not files and MyISAM tables. – Aloud

The 8.0 Data Dictionary will be "backed up" by flat files of some kind, just in case the InnoDB tables get too hosed. – Aloud

Also, FULLTEXT and SPATIAL are now fully implemented in InnoDB. – Aloud

Thanks @RickJames, I wrote the above comments in 2013 as you can tell, so that info is worth updating. – Giant