When to use MyISAM and InnoDB

Question

MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations  If your read to write insert update  ratio is less than 15  its better to use MyISAM   InnoDB uses row level locking  has commit  rollback  and crash-recovery capabilities to protect user data  It supports transaction and fault tolerance  above differences is correct between MyISAM and InnobDB  please guide if any other limitations are there for MYISAM and InnobDB  when should i use MyiSAM or when Innodb  Thank you

User · Accepted Answer

Read about Storage Engines   MyISAM   The MyISAM storage engine in MySQL    Simpler to design and create  thus better for beginners  No worries about the foreign relationships between tables  Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources  -- Mostly no longer true  Full-text indexing   -- InnoDB has it now Especially good for read-intensive  select  tables  -- Mostly no longer true  Disk footprint is 2x-3x less than InnoDB s   -- As of Version 5 7  this is perhaps the only real advantage of MyISAM    InnoDB    The InnoDB storage engine in MySQL    Support for transactions  giving you support for the ACID property   Row-level locking  Having a more fine grained locking-mechanism gives you higher concurrency compared to  for instance  MyISAM  Foreign key constraints  Allowing you to let the database ensure the integrity of the state of the database  and the relationships between tables  InnoDB is more resistant to table corruption than MyISAM  Support for large buffer pool for both data and indexes  MyISAM key buffer is only for indexes  MyISAM is stagnant  all future enhancements will be in InnoDB   This was made abundantly clear with the roll out of Version 8 0    MyISAM Limitations    No foreign keys and cascading deletes updates No transactional integrity  ACID compliance  No rollback abilities 4 284 867 296 row limit  2 32  -- This is old default   The configurable limit  for many versions  has been 2  56 bytes  Maximum of 64 indexes per table   InnoDB Limitations    No full text indexing  Below-5 6 mysql version  Cannot be compressed for fast  read-only  5 5 14 introduced ROW FORMAT COMPRESSED  You cannot repair an InnoDB table   For brief understanding read below links    MySQL Engines  InnoDB vs  MyISAM     A Comparison of Pros and Cons MySQL Engines  MyISAM vs  InnoDB What are the main differences between InnoDB and MyISAM  MyISAM versus InnoDB What  39 s the difference between MyISAM and InnoDB  MySql  MyISAM vs  Inno DB

User · Answer

Use MyISAM for very unimportant data or if you really need those minimal performance advantages  The read performance is not better in every case for MyISAM  I would personally never use MyISAM at all anymore  Choose InnoDB and throw a bit more hardware if you need more performance  Another idea is to look at database systems with more features like PostgreSQL if applicable  EDIT  For the read-performance  this link shows that innoDB often is actually not slower than MyISAM  https   www percona com blog 2007 01 08 innodb-vs-myisam-vs-falcon-benchmarks-part-1

[mysql] When to use MyISAM and InnoDB?

Examples related to mysql

Examples related to database

Examples related to database-schema