Search code examples
mysqlinnodbmyisam

In Which situation MYISAM AND INNODB Engine should be used

Give me some practical examples in which I can understand use of both myisam and innodb engine

Solution

  • The MyISAM storage engine is clearly being phased out, and I expect it will eventually be removed from MySQL.

    If you want to keep upgrading to newer version of MySQL, you should design your databases to never use MyISAM.

    MyISAM does not support any of the following:

    • ACID properties
    • foreign keys
    • transactions
    • clustered index
    • row-level locking
    • concurrent updates
    • crash recovery
    • data caching (only indexes are cached; data is cached by the filesytem)
    • partitioning (in MySQL 8.0)

    MyISAM is receiving no improvements by the MySQL engineering team, as far as I know.

    MyISAM is still used for the grant tables in the mysql.* schema as of MySQL 5.7, but in 8.0, those tables are all stored in InnoDB.

    On my last project, I used MyISAM for only one table. The table stored 5 billion rows and it needed to be stored on a server with limited disk space with no plan or budget for upgrading. MyISAM stored this table in half the space than InnoDB (your case may get a different storage ratio, depending on data types, indexes, and other factors). Using MyISAM, I could store this table on the server until it could be upgraded. Using InnoDB, the table would have been too large.