Which MySQL engine should i use where most operations are read operations

Question

I want to develop an application for which i will need a database to store data. I am using MySQL as backend. My question is Which MySQL engine should i use?

Well, i have mentioned some important characteristics of operations that will be performed on db:

• 99.99% read operations.
• I don't need foreign-keys.
• I don't need transactions.
• Simple read operations and nothing else.

I know that InnoDB is latest default engine in MySQL. But, as far as i can conclude, i don't need most of the features which are main reasons to use InnoDB. So, now i am left with MyISAM.

I am thinking of using MyISAM as i just want to read data from database. I have refered the official documentation for storage engines and also googled it. But, still may be that my knowledge-base is lacking some important updates. So, i am seeking help from community.

Is my choice of using MyISAM storage engine for specified scenario proper?

Thanking Community in anticipation.

I thank ypercube to help me narrow-down my choice.

• ypercubeᵀᴹ: What is this ISAM and where did you read about it? The official list shows MyISAM but no ISAM engine.
• Bhavik Shah: @ypercube: i have refered this
• ypercubeᵀᴹ: That's 10 years old article! See this: Storage Engines in 3.23/4.0/4.1 versions, where it states: "The original storage engine was ISAM, which managed nontransactional tables. This engine has been replaced by MyISAM and should no longer be used. It is deprecated in MySQL 4.1, and is removed in subsequent MySQL release series." I don't suppose you plan to use a 4.x version.
• Bhavik Shah: @ypercube your comment has helped me. I have updated my question. thanks :)

Answer 1

Although MyISAM genrally matches your characteristics, I advise you against using it, for the following reasons:

• Integrity, MyISAM is non-trasnactional: yes, I know you wrote you do not need transactions. The thing is, with MyISAM not even a single operation is "atomic". You see, a power failure in the middle of a write (you do have 0.01% writes), may corrupt your data; now even a single row has any integrity.

  You don't need transactions just for complex "grouping multiple queries" or "rollback to savepoint". You need transactions to know your data has integrity. MyISAM has nothing of the sort. There is no mechanism to guarantee you data. A corruption may (and will) occur, and either you will not even know it, or you will not be able to fix it. The closest you have is a REPAIR TABLE which at best will fix your table structure.

  I say if you case about your data -- MyISAM is a no-go.

• Lack of development: no one is actually improving much of the code. Everyone is busy making InnoDB better and better. It's not that by picking on MyISAM you will stay behind: you're starting waaay behind. Do not expect bugfixes as quick as you would for InnoDB.

• Poor for live backups. For taking a live backup of your database you will need to place a READ LOCK on your database for the entire operation. It may not be such a bid deal for you; you may also use replication and have the backup execute on the slave (but replication with MyISAM is again so unsafe due to lack of integrity and error-recovery on master). Or you can also use LVM.

• Bhavik Shah: Thanks. Your answer has certainly helped me decide. Apart from the fact that i have accepted your answer, i want to ask curiously that "Will it not be an overkill if we opt for InnoDB because of crash and bug of MyISAM and does not consider the speed of read-operation?" AFAIK, InnoDB is slower as compared to MyISAM when it comes to reading data from db.
• Shlomi Noach: In my personal view there is no such thing as "overkill" for your data's integrity. If you don't have data anymore, what's the point? I might add that you could try and benefit from both worlds: setup an InnoDB master, and a MyISAM slave (this can be done). Then query the slave. And make sure to benchmark queries against both master and slave so as to decide how much faster MyISAM is for you.

Answer 2

MyISAM has a drawback : concurrency (with its table-level lock). Even if you have a very little amount of write operations, it can block everything else and it depends on the duration of your read operations.

Here is the scenario on one table :

• You perform a SELECT and it will last for a few seconds
• You decide to run an UPDATE (or any write operation on this table) that takes milliseconds within the same time
• During this time, a write lock is put on the entire table and nobody, except running queries, can read or write. Everything that will use this table is queued. The only thing that can release the lock is to wait for the first SELECT to end, then the UPDATE to end.

Now, imagine if you multiple the number of read operations. It can be very ugly for a web-based application when every user will have to wait for seconds to load a page. But if most of your read operations only take milliseconds to perform. This problem won't be noticed.

InnoDB has solved this problem with the MVCC (Multiversion Concurrency Control) implementation that keeps multiple versions of a single row at the same time. It can lock at the row-level and not at the table like MyISAM.

There is some hints to mitigate this phenomenon like LOW_PRIORITY for your UPDATE, DELETE, or INSERT or HIGH_PRIORITY for your SELECT, and/or use the low-priority-updates system variable. For more information, you can read the official documentation here.

If this drawback isn't a problem for you and your application, you can go ahead and choose MyISAM.

The only way to have an accurate look at one storage engine or another within your environment is to test it with your production workload (or something like it).

• Bhavik Shah: have you read characteristics i have mentioned in question? If not, please read it. I have pristinely mentioned the scenario and want to know whether MyISAM is wise choice for that scenario or not. What you are suggesting are the features of InnoDB. Thanks for reply but it was not helpful to me. :)
• riouj: As I said, MyISAM is weak at concurrency, no matter the percentage of read/write you have. 'Simple', in the mean time, doesn't represent any of the time duration of your queries. It can be simple but long queries or simple and fast. Maybe fast now but for how long ? We don't know. It can be good to choose MyISAM but beware of this problem. It could be a big problem in the future, trust me.