MyISAM and InnoDB are two most commonly used storage engines of MySQL database. However, MyISAM is the default storage engine chosen by MySQL database, when creating a new table. The major differences between these two storage engines are :
- InnoDB supports transactions which is not supported by tables which use MyISAM storage engine.
- InnoDB has row-level locking, relational integrity i.e. supports foreign keys, which is not possible in MyISAM.
- InnoDB ‘s performance for high volume data cannot be beaten by any other storage engines available.
Tables created in MyISAM are known to have higher speed compared to tables in InnoDB. But since InnoDB supports volume, transactions, integrity it’s always a better option which you are dealing with a larger database. It is worth mentioning that a single database can have tables of different storage engines.
File structure
MyISAM stores each table on disk with three files whose names begin with same as table name. These files have different extensions to differentiate their purpose. A .frm files stores the table format, and a .MYD (MYData) file stores the data of the table. If the table has indexes then these are stored in the .MYI (MYIndex) files.
On the other hand, InnoDB tables and their indexes are stored in the tablespace, which consists of several files. That is why InnoDB tables can be very large and can store large volume of data. The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory.
Check the engines being used for existing tables
So these are the main differences between these two engines. You can specify in the query that which engine is to be used while creating the table.
CREATE TABLE test name varchar(30) ENGINE = InnoDB;
Since MyISAM is the default engine assigned when creating a table, so you need not to specify it, if you are planning to use MyISAM. This rule holds good, if you have not changed anything in the configuration. To check the engines of already existing tables, use the following query. It will list all the existing tables which are present in the current database, with their engines specified.
show table status;
If you want to view the status of a particular table, then use the following query,
show table status where Name = ‘tablename’;
Hope that helps.
Stay Digified !!
Sachin Khosla
