Back to Top

Learn about InnoDB MySQL Storage Engine

InnoDB – A MySQL Storage Engine

This is the posts where I’m going to cover tutorial and it is about InnoDB. MySQL database allows you to use a different MySQL storage engine for different tables and databases.

The storage engine is the engine used to store and retrieve data. Most popular storage engines are MyISAM and InnoDB. MyISAM storage engine can corrupt data easily as in my earlier article I have written that we need to repair MyISAM tables and it is difficult and in most of the cases it fails to repair. Whereas InnoDB is transactional, reliable than MyISAM.If the database crashes, InnoDB will attempt to fix crashed data by running the log file from the last timestamp. In most cases, it will succeed and the entire process is easy and transparent.

The InnoDB engine has been used when large amounts of data analysis and it is also suitable for many stages. However, there are still many developers still stubborn to use MyISAM storage engine. This article will change the point of view of the majority of developers. InnoDB is simple and easy and also supports fast COUNT (*) Query.InnoDB is more suitable for critical situations when more inserts and updates are done.

Some points about InnoDB:

  • InnoDB supports Foreign key Concepts.
  • InnoDB tables support transactions.
  • InnoDB tables recover well from crashes.
  • InnoDB is row-level rather than table-level locking.
  • InnoDB supports concurrent writes into the same table.
  • InnoDB supports schema definitions so we can manage relationships of table by schema also.

Let’s create tables with InnoDB storage Engines

Step 1:

First of all we create one table say Person which Engine is InnoDB.

CREATE TABLE person(  
	firstname varchar(30),  
	lastname varchar(30),  
	PRIMARY KEY (pid)  

Step 2:

Next, we will create a relation table of person and its Person_info_rel

CREATE TABLE person_info_rel (  
	pid int(5),
	email varchar(255)  ,

Step 3:

After creating the table, you need to create an index on the field which you want to use as a foreign key.You can use below query to create the index.

ALTER TABLE `person_info_rel` ADD INDEX ( pid )

After creating index, click on relation view shown in below screen:

Step 4:

After clicking on relation view, you will get below screen and you need to select value as per screen

OR you can use below query

ALTER TABLE person_info_rel 
ADD FOREIGN KEY ( pid ) REFERENCES test.person

Step 5:

If relations within tables are properly done with above steps, you will get below screen at the time you apply insert query.

NOTE: you can use mix types of Storage Engines within the same database.

With InnoDB, you can generate Schema or Relation view also which looks like below screen

Must Read:
To Repair MySQL Table
Transaction in MySQL

Advantages of InnoDB

  1. Its more useful when insert,update happend more because its supports row-level locking and we can made chagnes to the same row that is being inserted or updated.
  2. When Data Integrity is more important , InnoDB is useful.
  3. InnoDB is a high-reliability and high-performance storage engine.

Disadvantages of InnoDB

  1. InnoDB is more complex than MyISAM because we need to mange relationships between table and schemas created with data models.
  2. InnoDB consumes more resources than the can check on your PHPMyAdmin InnoDB table takes more space than MyISAM.
  3. InnoDB is not support full-text indexing.

What are your thoughts on InnoDb? If you have any questions, feel free to ask me via the comments section.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Most Popular Posts

What are Lambdas and Closures in PHP?

Posted on 2 years ago


How to change Quotes in WordPress

Posted on 6 years ago


New Features in MySQL 5.6

Posted on 7 years ago


How to enable Debug Console in X-cart?

Posted on 6 years ago


Hello world

Updated 1 month ago

Nick Carter

In depth Understanding of Laravel Core

Updated 3 months ago


ECMAScript 6 New Features – 2

Updated 1 year ago


The Reader’s Poll – June 2015

Posted on 4 years ago


The Reader’s Poll – August 2014

Posted on 5 years ago


The Reader’s Poll – June 2014

Posted on 5 years ago


The Readers’ Poll – May 2014

Posted on 5 years ago


The Readers’ Poll – August 2013

Posted on 6 years ago