What is the difference between InnoDB and MyISAM table types and how can I choose among the best in different situations? Is it possible to use the both MyISAM and InnoDB table types in a single database?
MyISAM and InnoDB are database storage engines used in open source database systems. By default, MyISAM is the default storage engine when the user has not selected a particular storage engine to use.
MyISAM is based on the ISAM code, which is known for its reliability, with various extensions. Data stored in MyISAM storage engine divides the data into three files: Table format, data and indexes.
The table format has an extension of .from, data has an extension of .MYD with the index file having an extension of .MYI. All data values are stored in low byte first while numeric values are stored in high byte first. The storage engine supports large files in systems that support large files.
InnoDB is a transaction safe storage engine with recover capabilities; this enables protection of user data. Its low level rocking allows for multi-user concurrency and performance. The storage engine is created for performance and can be mixed with other storage engines.
The main difference between the two engines is on performance where InnoDB has the highest performance and the format with which the data is stored.
The storage engines can be combined together.
