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

Question

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..

Answer 1

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.

Answer 2

You certainly could create InnoDB tables without foreign keys, but that is cutting out one of the main advantages of it: referential integrity. However, since MyISAM isn't built with the intent of referential integrity table keys can be stored differently, and perhaps more efficiently.

There are also some differences in locking and access. InnoDB supports row level locking, whereas MyISAM only supports table-level locking. Depending on the queries you're performing (SELECTS versus INSERTS/UPDATES) this can have a noticeable effect on performance.

Answer 3

I am not sure if this is no longer true MyISAM is faster than InnoDB for reads.

Also, MyISAM tables are stored in separate files and (from what I can remember) you can actually transport those files to another MySQL database and is easier to backup.

By default InnoDB databases are stored in one huge glob on the file system.

As for why it is still being widely used, I always figured it was because it is the default option. Personally, I still believe that the advantages of InnoDB triumphs MyISAM and MyISAM also has problems with data integrity from my experience.

Answer 4

I had to list some advantages of MyISAM over InnoDB

1. FULLTEXT search

2. ...

3. no, that's it.

(OK, there are some cases where MyISAM is faster than InnoDB, but rarely enough that it's worth putting up with the lack of ACID-compliance. Today the main reason for doing anything with MyISAM is to get fulltext search which is sadly not supported in InnoDB.)

Answer 5

You prolly need to read up on the Mysql Peformance blog.