What s the difference between MyISAM and InnoDB

Question

I understand that this question has been asked before  but most of the time it is asked in relation to a specific database or table  I cannot find an answer on this site that describes the two engines and their differences without respect to someones specific database    I want to be able to make more informed decisions in the future with respect to designing a table or database  so am looking for a comprehensive answer on the differences between the two storage engines   What s the difference between MyISAM and InnoDB  and what should I be looking for when trying to decide between one or the other

User · Answer

MYISAM    MYISAM supports Table-level Locking MyISAM designed for need of speed MyISAM does not support foreign keys hence we call MySQL with MYISAM is DBMS MyISAM stores its tables  data and indexes in diskspace using separate three different files   tablename FRM  tablename MYD  tablename MYI  MYISAM not supports transaction  You cannot commit and rollback with MYISAM  Once you issue a command it   s done  MYISAM supports fulltext search You can use MyISAM  if the table is more static with lots of select and less update and delete    INNODB    InnoDB supports Row-level Locking InnoDB designed for maximum performance when processing high volume of data InnoDB support foreign keys hence we call MySQL with InnoDB is RDBMS InnoDB stores its tables and indexes in a tablespace InnoDB supports transaction  You can commit and rollback with InnoDB

User · Answer

The main differences between InnoDB and MyISAM    quot with respect to designing a table or database quot  you asked about  are support for  quot referential integrity quot  and  quot transactions quot   If you need the database to enforce foreign key constraints  or you need the database to support transactions  i e  changes made by two or more DML operations handled as single unit of work  with all of the changes either applied  or all the changes reverted  then you would choose the InnoDB engine  since these features are absent from the MyISAM engine  Those are the two biggest differences  Another big difference is concurrency  With MyISAM  a DML statement will obtain an exclusive lock on the table  and while that lock is held  no other session can perform a SELECT or a DML operation on the table  Those two specific engines you asked about  InnoDB and MyISAM  have different design goals  MySQL also has other storage engines  with their own design goals  So  in choosing between InnoDB and MyISAM  the first step is in determining if you need the features provided by InnoDB  If not  then MyISAM is up for consideration  A more detailed discussion of differences is rather impractical  in this forum  absent a more detailed discussion of the problem space    how the application will use the database  how many tables  size of the tables  the transaction load  volumes of select  insert  updates  concurrency requirements  replication features  etc   The logical design of the database should be centered around data analysis and user requirements  the choice to use a relational database would come later  and even later would the choice of MySQL as a relational database management system  and then the selection of a storage engine for each table

[mysql] What's the difference between MyISAM and InnoDB?

Examples related to mysql

Examples related to database

Examples related to innodb

Examples related to myisam