- The main difference between MyISAM and InnoDB is that InnoDB supports transaction.
- InnoDB supports some newer features: Transactions, row-level locking, foreign keys.
- InnoDB is for high volume, high performance.
MyISAM is of course the default table type i’m MySQL
Some says MyISAM is fast and some says that InnoDB is ?
Let me explain you, If your data is very large and you have to run more more query there per minutes, or we can say if you need transaction more.INSERT, UPDATE query are executed per minutes.
and table contains very data,then InnoDB will the best solution, according to all of your need , it will prove fetch,insert,update all will fast. InnoDB has been designed for maximum performance when processing large data volumes.
If your data integration so much high,then it’s better to user InnoDB.
But if you haven’t so much data and preparing small application, queries inseting fetching data is not too much,then it is better to use MyISAM, It will work faster rather then InnoDB in this case.
The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a table space, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be of any size even on operating systems where file size is limited to 4GB.
In hard disk tables of MyISAM is low weighted (contains less memory space) rather than InnoDB.
Locking Difference in MyISAM and InnoDB?
MyISAM provide the table level locking means if the data in one table has been modified by the other table ,the entire table will lock for the next process.But InnoDB provide the row level locking only the row of the table that is being updated is locked.
MyISAM provide a full variety of datatypes,but InnoDB will not do.
MyISAM provide full text search which is not supported by InnoDB.