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.
1 2 3 4 5 6 7 8 | CREATE TABLE person( pid int(5) NOT NULL AUTO_INCREMENT, firstname varchar(30), lastname varchar(30), PRIMARY KEY (pid) ) ENGINE=InnoDB; |
Step 2:
Next, we will create a relation table of person and its Person_info_rel
1 2 3 4 5 6 7 8 | CREATE TABLE person_info_rel ( id int(5) NOT NULL AUTO_INCREMENT, pid int(5), email varchar(255) , PRIMARY KEY (id) ) ENGINE=InnoDB; |
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.
1 2 3 | 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
1 2 3 4 5 6 7 | ALTER TABLE person_info_rel ADD FOREIGN KEY ( pid ) REFERENCES test.person (id) ON DELETE CASCADE ON UPDATE CASCADE ; |
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:
EXISTS and NOT EXISTS in MySQL
To Repair MySQL Table
Transaction in MySQL
Advantages of InnoDB
- 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.
- When Data Integrity is more important , InnoDB is useful.
- InnoDB is a high-reliability and high-performance storage engine.
Disadvantages of InnoDB
- InnoDB is more complex than MyISAM because we need to mange relationships between table and schemas created with data models.
- InnoDB consumes more resources than the MyISAM.you can check on your PHPMyAdmin InnoDB table takes more space than MyISAM.
- 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.
