KNOWNHOST WIKI

User Tools

Site Tools


developmental:mysql-myisam-innodb

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
developmental:mysql-myisam-innodb [2020/05/07 07:03]
Karson N.
developmental:mysql-myisam-innodb [2020/05/13 13:41] (current)
Karson N.
Line 3: Line 3:
 When you create a table in MySQL,((or MariaDB)) you can choose a //storage engine//. The storage engine is the way the table data is stored in files.((or sometimes memory)) There exist several different storage engines, but the most commonly used ones are MyISAM and InnoDB, each of which are the default storage engine in different MySQL versions. If you do not specify a storage engine when creating a table, the default engine for your MySQL version will be used. In MySQL versions earlier than 5.5.5, MyISAM was default, but in versions 5.5.5 and later, InnoDB is the default.(([[http://​dev.mysql.com/​doc/​refman/​5.5/​en/​myisam-storage-engine.html|citation]])) In most cases, MyISAM is recommended,​ but there are some cases where for a particular table InnoDB might be recommended instead. When you create a table in MySQL,((or MariaDB)) you can choose a //storage engine//. The storage engine is the way the table data is stored in files.((or sometimes memory)) There exist several different storage engines, but the most commonly used ones are MyISAM and InnoDB, each of which are the default storage engine in different MySQL versions. If you do not specify a storage engine when creating a table, the default engine for your MySQL version will be used. In MySQL versions earlier than 5.5.5, MyISAM was default, but in versions 5.5.5 and later, InnoDB is the default.(([[http://​dev.mysql.com/​doc/​refman/​5.5/​en/​myisam-storage-engine.html|citation]])) In most cases, MyISAM is recommended,​ but there are some cases where for a particular table InnoDB might be recommended instead.
  
 +\\
 ===== General Comparison ===== ===== General Comparison =====
  
 Here is a very brief overview of common types of situations where one of these two engines might be better to use than the other. Here is a very brief overview of common types of situations where one of these two engines might be better to use than the other.
  
 +\\
 ==== When to use MyISAM instead of InnoDB ==== ==== When to use MyISAM instead of InnoDB ====
  
   * In general, if a MyISAM table has a problem, the problem will be limited to that table, rather than affecting the functionality of other tables or databases as can sometimes happen when an InnoDB table has a problem. For this reason, MyISAM is recommended whenever possible in servers with multiple sites.   * In general, if a MyISAM table has a problem, the problem will be limited to that table, rather than affecting the functionality of other tables or databases as can sometimes happen when an InnoDB table has a problem. For this reason, MyISAM is recommended whenever possible in servers with multiple sites.
  
 +\\
 ==== When to use InnoDB instead of MyISAM ==== ==== When to use InnoDB instead of MyISAM ====
  
Line 17: Line 20:
   * In part because issues with InnoDB tables can affect access to other databases, if a site has need for enough concurrent table writes to justify using InnoDB, that site might be busy enough to start considering putting it on its own server. If the site is not yet big enough for its own server, it might be better instead to recode the site to require less concurrency of table writes.   * In part because issues with InnoDB tables can affect access to other databases, if a site has need for enough concurrent table writes to justify using InnoDB, that site might be busy enough to start considering putting it on its own server. If the site is not yet big enough for its own server, it might be better instead to recode the site to require less concurrency of table writes.
  
 +\\
 ==== When to use neither MyISAM nor InnoDB ==== ==== When to use neither MyISAM nor InnoDB ====
  
Line 22: Line 26:
   * Similarly, though it is even more rare, certain kinds of site cache data if being stored in a database table should probably use the MEMORY storage engine.   * Similarly, though it is even more rare, certain kinds of site cache data if being stored in a database table should probably use the MEMORY storage engine.
  
 +\\
 ==== How to Change a Table'​s Storage Engine ==== ==== How to Change a Table'​s Storage Engine ====
  
Line 48: Line 53:
 Congratulations! You have now changed the storage engine of the table. Congratulations! You have now changed the storage engine of the table.
  
 +\\
 ===== More Technical Explanation ===== ===== More Technical Explanation =====
  
 Here is a little bit of more detailed information about the storage engines, to help explain //why// different ones are better for different purposes. Here is a little bit of more detailed information about the storage engines, to help explain //why// different ones are better for different purposes.
  
 +\\
 ==== Similarities ==== ==== Similarities ====
  
   * Any database table, regardless of which storage engine it uses, will have a ''​%%.frm%%''​ file. This file's name will be the table name followed by this file extension. This file contains table metadata, such as the table definition. Depending on the table'​s engine((and sometimes the way the engine is configured)) there may or may not be other files associated with the table.   * Any database table, regardless of which storage engine it uses, will have a ''​%%.frm%%''​ file. This file's name will be the table name followed by this file extension. This file contains table metadata, such as the table definition. Depending on the table'​s engine((and sometimes the way the engine is configured)) there may or may not be other files associated with the table.
  
 +\\
 ==== MyISAM ==== ==== MyISAM ====
  
Line 62: Line 70:
   * Because MyISAM stores all table information directly within that table'​s three files, it is possible if needed to restore a single database from a Partial VPS Restore, if //all// tables in that database are using MyISAM.   * Because MyISAM stores all table information directly within that table'​s three files, it is possible if needed to restore a single database from a Partial VPS Restore, if //all// tables in that database are using MyISAM.
  
 +\\
 ==== InnoDB ==== ==== InnoDB ====
  
Line 72: Line 81:
   * Additionally,​ if InnoDB crashes and is unable to recover automatically,​ it is possible for some of the InnoDB tables to be corrupted in a non-repairable way. This is another reason it is particularly important to have automatic backups enabled for any account using InnoDB tables.   * Additionally,​ if InnoDB crashes and is unable to recover automatically,​ it is possible for some of the InnoDB tables to be corrupted in a non-repairable way. This is another reason it is particularly important to have automatic backups enabled for any account using InnoDB tables.
  
 +\\
 ==== MEMORY ==== ==== MEMORY ====
  
Line 81: Line 91:
   * MEMORY tables should //only// be used for ephemeral, non-persistant data, that is only needed for short periods of time.   * MEMORY tables should //only// be used for ephemeral, non-persistant data, that is only needed for short periods of time.
  
 +\\
 ===== Summary ===== ===== Summary =====
  
developmental/mysql-myisam-innodb.txt · Last modified: 2020/05/13 13:41 by Karson N.