- MySQL Home
- MySQL tutorial
- MySQL Installation
- MySQL Statements
- MySQL Data Types
- Mysq CreateDatabase
- MySQL Create Table
- MySQL Constraints
- MySQL Primary key
- MySQL Foreign key
- Auto Increment Field
- MySQL Default Cons
- MySQL Insert Into
- MySQL Update
- MySQL Drop
- MySQL Delete
- MySQL Select
- MySQL Where
- MySQL Group By
- MySQL Having clause
- MySQL Order By
- MySQL Alter table
- MySQL Alias
- MySQL Create Index
- MySQL Subqueries
- MySQL Operators
- Mysql IsNull IsNotNull
- MySQL And or
- MySQL Between
- MySQL In Operator
- MySql Like Operator
- Mysql Union Operator
- Mysql Storage Engine
- Mysql Stored Procedure
- Mysql if Statement
- Mysql Case Statement
- MySQL Loops
- MySQL Cursor
mySQL Storage Engines
what is a storage engine?
Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.
Each of these different techniques and suites of functionality within the MySQL system is referred to as a storage engine (also known as a table type).
mysql provides various storage engines for its tables as below:
create table table_name ( variable_name data_type(size), variable_name data_type(size), .... ) engine = storage_engine_type;
create table table_name ( variable_name data_type(size), variable_name data_type(size), .... ) type = storage_engine_type;
the myisam tables are optimized for compression an speed.myisam tables are also portable platforms and oses.
the size of myisam table can be up to 256tb.the myisam tables are not transaction-safe.
All numeric key values are stored with the high byte first to permit better index compression. NULL values are permitted in indexed columns. This takes 0 to 1 bytes per key.
create table data( firstname varchar(10), lastname varchar(20) ) engine = myisam;
it is the most widely used storage engine with transaction support.it is an acid compliant storage engine.it supports row-level locking, crash recovery and multi-version concurrency control.it is the only engine which provides foreign key referential integrity constraint.
the innodb storage engine maintains its own buffer pool for caching data and indexes in main memory.the size of the innodb table can be up to 64tb.
create table data( firstname varchar(10), lastname varchar(20) ) engine = innodb;
A MERGE table is a virtual table that combines multiple MyISAM tables, which has similar structure, into one table. The MERGE storage engine is also known as the MRG_MyISAM engine.The MERGE table does not have its own indexes; it uses indexes of the component tables instead.
using merge table, you can speed up performance in joining multiple tables.
create table data( firstname varchar(10), lastname varchar(20) ) engine = merge;
In the MySQL - Memory storage engine creates tables in memory.It is the fastest engine. It provides table-level locking. It does not support transactions.Memory storage engine is ideal for creating temporary tables or quick lookups.The data is lost when the database is restarted.
CSV stores data in csv files. It provides great flexibility, because data in this format is easily integrated into other applications.
CSV table does not support NULL data type and read operation requires a full table scan
Archive storage engine is optimized for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing, retrieving large amounts of seldom referenced historical, archived data.
In MySQL Database archive type engine tables only allow INSERT and SELECT commands. The archive tables do not support indexes, so reading records requires a full table scanning.
create table data( firstname varchar(10), lastname varchar(20) ) engine = archive;
Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. It is good for distributed environments.
The local federated table stores no data. When you query data from a local federated table, the data is pull automatically from the remote federated tables.
create table data( firstname varchar(10), lastname varchar(20) ) engine = federated;