MySQL ENGINE

How to check Engine?

            mysql> use <Databse_Name>;

            mysql> show table status\G

ENGINES in MySQL 

Sr. No.	INODDB	MyISAM
1	It's New Engine	It's Old Engine
2	InnoDB is more complex	MyISAM is Simpler
3	InnoDB is stricter in data integrity	MyISAM is loose in data integrity
4	InnoDB implements row-level lock for inserting	MyISAM implements table-level lock.
	and updating
5	InnoDB has foreign keys and relationship	MyISAM does not.
	Contraints
6	InnoDB has better crash recovery	MyISAM is poor at recovering data integrity at system crashes
7	InnoDB does not have full-text search index.	MyISAM has full-text search index
8	Not ACID compliant and non-transactional	ACID compliant and hence fully transactional with
		ROLLBACK and COMMIT also support Foreign Key.

 

Advantages and Disadvantages 

            In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other

 

Advantages of InnoDB

            a. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.

            b. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

 

Disadvantages of InnoDB

            a. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM. 

            b. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.

            c. No full-text indexing.

 

Disadvantages of MyISAM

            a. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers. 

            b. Doesn’t support transactions which are essential in critical data applications such as that of banking.

            c. Slower than InnoDB for tables those are frequently being inserted to or updated, because the entire table is locked for any insert or update.

 

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.