I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's going to be a slow process, focusing on the most write-intensive tables at first).

Is there anything wrong with running a dedicated server where both types of storage engines exist?

  • Is there anything wrong with running a dedicated server where both types of storage engines exist? Maybe rephrase to Multiple types? – John Jan 7 '11 at 16:46
  • The only reason I said 'both' is because this question is about the two main engines that are configured to be 'finely tuned'. I'm trying to ignore other types of engines like MEMORY or MERGE, which are uncommon enough that afaik aren't the object of performance tuning. – Derek Downey Jan 7 '11 at 16:55

There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types.

For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be regenerated easily and it's a small table, say for queuing, you might choose Memory. If you have a table that's 90% reads, and the data has got to be there when you look for it, then you'd probably choose a storage engine that supports transactions and configurable atomicity, such as InnoDB. If you want accessibility through the file system w/o damaging data, you might choose CSV.

Nonetheless, you can safely use multiple storage engines within the same schema as well as the physical host.

Let me note though, that your buffers play a role in this whole mess. If you use both MyISAM and InnoDB, you will need to be careful that your key_buffer and innodb_buffer_pool do not contend. This will take careful planning on your part, but that's what we do.

  • 3
    +1 Another common use case is MyISAM for tables requiring full text search and InnoDB for all other tables. – Asaph Dec 19 '11 at 18:52
  • @Aseph: Even at the time of your comment, InnoDB supported Fulltext indices. – BlueRaja - Danny Pflughoeft May 29 '13 at 21:13
  • 2
    ^^^^ "MySQL 5.6 features" was not GA until 2013-02-05 – randomx May 30 '13 at 18:56
  • 1
    @randymelder Nice answer. Would you be able to elaborate on what you meant by "You will need to be careful that your key_buffer and innodb_buffer_pool do not contend"? – Neel Oct 16 '14 at 20:25
  • 1
    I want to be proved wrong, but the logic looks opposite of this stackoverflow.com/a/6796566/5645769 here. – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Jan 11 '16 at 4:36

I can't tell you if this is a common practice. I can say about my own experience.

I always use the best tool for the job, so I mix engines all the time. Most of my projects use MyISAM as the default engine.

When I need special features just available on InnoDB, I go for it.

When a table is mostly read-only, I choose Archive engine before I could blink.

Knowing that machine server has enough memory, all my temp data are store on Heap tables.

I saw in the past some slowdowns mixing MyISAM and InnoDB but this is not a specific MySQL problem. It's a design problem not seeming when you use just one engine. Actually using the wrong engine causes more slowdown doesn't matter if it is just MyISAM, just InnoDB or a mix of both. It's hard to define a formula to know when the slowdown would happen. Just actual tests could say it to you.

Of course, you couldn't preserve integrity and consistency mixing InnoDB and MyISAM on an unique query.

  • archive engine doesn't support indexing so why you store it in archive. – user4951 Jun 14 '12 at 8:24

I try to avoid mixing MyISAM and InnoDB tables in the same database, but this is for sanity rather than practical reasons. However, I find it useful to have a database with MyISAM tables for fulltext search so I can run that on sites. Keeping it in a separate database with a foreign key for each entry makes it easy for any other developers working on the DB to see what's going on.

  • How can you have a foreign keys when you use MyISAM ? – a_horse_with_no_name Apr 7 '14 at 18:17
  • Wrong terminology, but works like a foreign key. I store the id number of the item from the InnoDB table and use that to do the page lookup on search result. – Kenzo Apr 7 '14 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.