A brief about Mysql Storage Engine

mohideem

A storage engine is a software Component that a database management system uses to create CRUD operation.

Different Type of Storage Engines are available in Mysql

1. INNODB
2. MYISAM
3. HEAP or MEMORY
4. MERGE
5. CSV
6. ARCHIVE
7. BLACKHOLE
8. FEDERATED

INNODB:

1. Innodb is a default storage engine for MySQL 5.5 and above.
2. Innodb table supports the transaction.
3. It supports row level locking(It will lock the entire row until the process complete).
4. Innodb table size can be 64 Terabyte
5. It supports foreign key constraints to maintain data.
6. It provides high performance when processing a large amount of data.

Google Certified Agency

What is transaction

A Transaction is a group of operation. It will not be completed until all the operation is completed. If anyone of the operation fails the entire transaction fail.

Example

MYISAM:

1. MyISAM is default storage engine for prior MySQL 5.5
2. It does not support transactions
3. Its supports table-level locking(When the time of updating or inserting any query to a table it will the lock entire table. We can not execute another query until the previous process will complete).
4. MyISAM table size can be 256 Terabyte.

HEAP or MEMORY:

1. Memory storage engine creates tables in memory.
2. It does not support the transaction.
3. It is the fastest engine because it stores all the data in RAM.
4. It’s very useful for quick look-up of the identical data.
5. We can use this table as temporary.
6. When the MySQL server restarts or halts will lose the data.

MERGE:

1. It is also called as MRG_MYISAM.
2. It’s a collection of Identical MyISAM Table that can be merged with a single Table.
3. While creating a table we must use Union(List of all the tables)o

CSV:

1. Stores data in a comma(,) separated value in a CSV file.
2. It’s always compiled into the MySQL server.
3. We can easily import to another database.
4. It does not support transactions.
5. All the fields or column must have the Not Null in a CSV table.
6. It does not support indexing.

Example data Format

create table customersInfo(id integer(11) not null,name varchar(50) not null,email varchar(100) not null,phone varchar(10) not null) ENGINE=CSV;

1,”James”,”james@gmail.com”,”9099090092″
2,”Irfan”,”irfan@gmail.com”,”9091090092″,
3,”Robert”,”robert@gmail.com”,”9986090092″,

ARCHIVE:

1. It stores data as a compressed format.
2. It’s very fast while inserting data.
3. Its stores and retrieves a large amount of data in compressed format.
4. It does not support transactions.
5. It does not support Delete, Update OR Replace.
6. It supports only Select, Inserts and Orders by operations.

BLACKHOLE
1. Blackhole storage engine tables accept data but return an empty.
2. When you create a table it will create a file format as (.frm)
3. This storage engine used to perform performance test.

FEDERATED

1 .This engine is used to access the remote server without using replication server.
2. When you query in the local table it will automatically pull data from the remote-server.
3. No data stored on the local server.
4. It is very usefull for distributed environment.

Example:

CREATE TABLE employeeTable (

empId INT(3) NOT NULL AUTO_INCREMENT,
empName VARCHAR(42) NOT NULL DEFAULT ”,
emailId VARCHAR(100) NOT NULL DEFAULT ”,
PRIMARY KEY (empId)
)
ENGINE=FEDERATED
CONNECTION=’mysql://remoteServer@remote_host:9306/dbName/employeeTable’;
How to set database engine in a table

create table employee(empId integer(11) primary key,firstName varchar(25),lastName varchar(25),emailId varhcar(100) ) ENGINE=’INNODB’;

How to see list of table with Engine

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘dbname’;

This query will provide you to see the list of table along with the engine Type.

mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘sample_db’;

SHOW TABLE STATUS FROM DatabaseName;

How to alter engine

ALTER TABLE TABLE_NAME ENGINE=’MYISAM’;

Discuss your proeject

 

about the author

mohideem

  1. iPrism Technologies

    August 11, 2017

    Thank you for sharing your article. This is very informative article to My SQL storage engines.
    Keep it up.