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

MySQLstorage-engine

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.

Best Answer

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.

Related Question