{"id":26,"date":"2009-01-01T07:46:24","date_gmt":"2009-01-01T07:46:24","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=26"},"modified":"2023-09-05T07:48:32","modified_gmt":"2023-09-05T07:48:32","slug":"choosing-between-myisam-and-innodb-mysql-storage-engines","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/choosing-between-myisam-and-innodb-mysql-storage-engines","title":{"rendered":"Choosing between MyISAM and INNODB \u2013 MySQL Storage Engines"},"content":{"rendered":"<p>After reading at a lot of places for the the single repeatative question, &#8220;What engine shall I choose &#8211; MyISAM or Innodb?&#8221;, this is what I&#8217;ve got.<\/p>\n<p><strong>Following are points of consideration for MyISAM &#8211; MySQL storage engine:<\/strong><\/p>\n<ul>\n<li>Tables are really fast for select-heavy loads<\/li>\n<li>Table level locks limit their scalability for write intensive multi-user environments.<\/li>\n<li>Smallest disk space consumption<\/li>\n<li>Fulltext index<\/li>\n<li>Merged and compressed tables.<\/li>\n<\/ul>\n<p><strong>Following are points of consideration for InnoDB &#8211; MySQL storage engine:<\/strong><\/p>\n<ul>\n<li>ACID transactions<\/li>\n<li>Row level locking<\/li>\n<li>Consistent reads &#8211; allows you to reach excellent read write concurrency.<\/li>\n<li>Primary key clustering &#8211; gives excellent performance in some cases.<\/li>\n<li>Foreign key support.<\/li>\n<li>Both index and data pages can be cached.<\/li>\n<li>Automatic crash recovery &#8211; in case MySQL shutdown was unclean InnoDB tables will still recover to the consistent state- No check \/ repair like MyISAM may require.<\/li>\n<li>All updates have to pass through transactional engine in InnoDB, which often decreases performance compared to non-transactional storage engines.<\/li>\n<\/ul>\n<p><strong>Also consider:<\/strong><\/p>\n<ul>\n<li>Choose MyISAM for large constant tables or logging tables, relatively infrequent updates or Fast selects &#8211; these will not lock the table for the long time and thus it will not reduce performance.<\/li>\n<li>Choose MyISAM if you really don\u2019t need InnoDB.<\/li>\n<li>Choose InnoDB storage engine when following is required:<\/li>\n<li>Intensively updated tables &#8211; which can have many long selects running at the same time.<\/li>\n<li>Multi-statement transactions Advanced isolation levels and row-level locking Foreign key constraints. Well we can say for regular usage: MyISAM is for speed and InnoDB for data integrity.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"After reading at a lot of places for the the single repeatative question, &#8220;What engine shall I choose &#8211; MyISAM or Innodb?&#8221;, this is what I&#8217;ve got. Following are points&hellip;\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[358,262,427,107,426],"class_list":{"0":"post-26","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-innodb","9":"tag-myisam-and-innodb","10":"tag-mysql","11":"tag-storage-engine","12":"tag-technical"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/26","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=26"}],"version-history":[{"count":2,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/26\/revisions"}],"predecessor-version":[{"id":1829,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/26\/revisions\/1829"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=26"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=26"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=26"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}