Search code examples
mysqlinnodbmyisamdatabase-cluster

Is it true that MyISAM engine is more preferable than InnoDB when we are building clustered storage? Why if it is so?

I heard this today during interview for java developer. I had to list some advantages of MyISAM over InnoDB and why it's still being widely used. And they were waiting to hear from me the answer as the title of this question.

As I understand from their own answer: MyISAM doesn't have foreign keys and DB can be easily clustered (one table per server for example). But why can't we simply create InnoDB tables without foreign keys? This explaination sounds strange to me..

Solution

  • There is no silver bullet answer here. You need to know the pros and cons of each before you make a decision on which one you use for any particular application.

    InnoDB:

    • supports FK's
    • supports transactions
    • uses a large memory buffer for operation
    • supports row level locking
    • But has a much higher maintenance cost -- you really need to tune your memory usage, configure your table files, etc.

    MyISAM:

    • has a bunch of special column features that InnoDB doesn't, like:
      • full text indexes
      • spatial columns (I'm pretty sure this doesn't work with InnoDB)
    • Very fast for primary read/append use cases (table locks for updates, deletes, but not for inserts)
    • Also typically has faster inserts
    • caches indexes in memory (key buffer), but relies on the OS to buffer the actual data pages

    For example, I'd use InnoDB for things like ecommerce, user databases or anything that I want to use transactions in.

    For data warehouses, logging, reporting, etc I'd probably use MyISAM.