Storage engines (underlying software component) are MySQL components, that can handle the SQL operations for different table types to store and manage information in a database. InnoDB is mostly used general-purpose storage engine and as of MySQL 5.5 and later it is the default engine. There are many storage engines available in MySQL and they are used for different purposes.
Version : MySQL 5.6
Storage engines of MySQL
Engines Description InnoDBThis is the default storage engine for MySQL 5.5 and higher. It provides transaction-safe (ACID compliant) tables, supports FOREIGN KEY referential-integrity constraints. It supports commit, rollback, and crash-recovery capabilities to protect data. It also support row-level locking. It's "consistent nonlocking reads" increases performance when used in a multiuser environment. It stores data in clustered indexes which reduces I/O for queries based on primary keys.
MyISAM This storage engine, manages non transactional tables, provides high-speed storage and retrieval, supports full text searching. MEMORY Provides in-memory tables, formerly known as HEAP. It sores all data in RAM for faster access than storing data on disks. Useful for quick looks up of reference and other identical data. MERGE Groups more than one similar MyISAM tables to be treated as a single table, can handle non transactional tables, included by default. EXAMPLE You can create tables with this engine, but can not store or fetch data. Purpose of this is to teach developers about how to write a new storage engine. ARCHIVE Used to store a large amount of data, does not support indexes. CSV Stores data in Comma Separated Value format in a text file. BLACKHOLE Accepts data to store but always returns empty. FEDERATED Stores data in a remote database.Other Topics :
List of Storage Engines supported by your MySQL installation
Differences between InnoDB and MyISAM
List of Storage Engines supported by your MySQL installation
The following command display the status information of the server's storage engines.
mysql> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.28 sec)
Setting the Storage Engine
In CREATE TABLE STATEMENT you can add ENGINE table option to mention a storage engine. See the following CREATE TABLE statements, where different engines have used :
CREATE TABLE t1 (i INT) ENGINE = INNODB; CREATE TABLE t2 (i INT) ENGINE = CSV; CREATE TABLE t3 (i INT) ENGINE = MEMORY;
In MySQL 5.6, the default engine is InnoDB. The default storage engine is used if you do not mention the other engine name in ENGINE option. You can specify the default engine by using the --default-storage-engine server startup option (Command-Line Format), or by setting the default-storage-engine option in the my.cnf configuration file.
You can set the default storage engine for the current session by setting the default_storage_engine variable using set command.
SET default_storage_engine=ARCHIVE;
If you want to convert a table form one storage engine to another, use an ALTER TABLE statement. See the following statement :
ALTER TABLE table1 ENGINE = InnoDB;
To store the table and column definitions for a new table, MySQL always creates an .frm file. Depending on the storage engine the table's index and data may be stored in one or more other files. The server creates the .frm file above the storage engine level.
MySQL: InnoDB Storage Engine
InnoDB is a storage engine for MySQL that balances high reliability and high performance. As of MySQL 5.5 and later, it is the default storage engine.
Feaures of InnoDB storage engine :
Storage limits 64TB Transactions Yes Locking granularity Row MVCC (Multiversion concurrency control) Yes Geospatial data type support Yes Geospatial indexing support No B-tree indexes Yes T-tree indexes No Hash indexes No Full-text search indexes Yes Clustered indexes Yes Data caches Yes Index caches Yes Compressed data Yes Encrypted data Yes Cluster database support No Replication support Yes Foreign key support Yes Backup / point-in-time recovery Yes Query cache support Yes Update statistics for data dictionary YesAdvantages of InnoDB storage engine
Creating InnoDB tables :
Use CREATE TABLE statement to create am InnoDB table without any special clauses. As of MySQL 5.5, it is the default MySQL storage engine. In MySQL 5.6, issuing the CREATE TABLE statement without an ENGINE= clause creates an InnoDB table. Here is an example :
mysql> CREATE TABLE table1 (col1 INT, col2 CHAR(30), PRIMARY KEY (col1)); Query OK, 0 rows affected (1.11 sec) mysql> DESC table1; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | col1 | int(11) | NO | PRI | 0 | | | col2 | char(30) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.21 sec)
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to 'tutorial' database).
mysql> SHOW TABLE STATUS FROM tutorial; +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2014-02-14 12:08:34 | NULL | NULL | utf8_general_ci | NULL | | | +--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec)
Handling AUTO_INCREMENT in InnoDB :
InnoDB provides a method that improves scalability and performance of SQL statements that insert rows into tables with AUTO_INCREMENT columns. To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column (col1 in the example) must be defined as part of an index. See the following example :
mysql> CREATE TABLE table1 (col1 INT(10) NOT NULL AUTO_INCREMENT, col2 CHAR(30), PRIMARY KEY (col1)); Query OK, 0 rows affected (0.50 sec)
Handling FOREIGN KEY Constraints in InnoDB :
MySQL supports foreign keys, which let you cross-reference related data across tables, and foreign key constraints, which help keep this spread-out data consistent. Foreign key definitions for InnoDB tables are subject to the following conditions :
Limitation: InnoDB table :
MySQL: MyISAM Storage Engine
MyISAM storage engine is based on the older ISAM storage engine (not available now) but has many useful extensions.
Features of MyISAM storage engine :
Storage limits 256TB Transactions No Locking granularity Table MVCC (Multiversion concurrency control) No Geospatial data type support Yes Geospatial indexing support Yes B-tree indexes Yes T-tree indexes No Hash indexes No Full-text search indexes Yes Clustered indexes No Data caches No Index caches Yes Compressed data Yes Encrypted data Yes Cluster database support No Replication support Yes Foreign key support No Backup / point-in-time recovery Yes Query cache support Yes Update statistics for data dictionary YesEach MyISAM table is stored on disk in three files.
Creating MyISAM tables :
Use CREATE TABLE statement to create am MyISAM table with ENGINE clause. As of MySQL 5.6, it is necessary to use ENGINE clause to specify the MyISAM storage engine because InnoDB is the default engine. Here is an example :
mysql> CREATE TABLE table2 (col1 INT, col2 CHAR(30)) ENGINE = MYISAM;
Query OK, 0 rows affected (0.19 sec)
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to 'tutorial' database).
mysql> SHOW TABLE STATUS FROM tutorial;
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | |
| table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | |
+--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.07 sec)
Main characteristics of MyISAM tables :
Corrupted MyISAM Tables :
MyISAM table format is very reliable, but in some occasion you can get corrupted tables if any of the following events occur :
MySQL: MEMORY Storage Engine
The MEMORY storage engine creates tables that are stored in memory. Because the data can be crashed due to hardware or power issues, you can only use these tables as temporary work areas or read-only caches for data pulled from other tables. When the MySQL server halts or restarts, the data in MEMORY tables is lost.
Features of MEMORY storage engine :
Storage limits RAM Transactions No Locking granularity Table MVCC No Geospatial data type support No Geospatial indexing support No B-tree indexes Yes T-tree indexes No Hash indexes Yes Full-text search indexes No Clustered indexes No Data caches N/A Index caches N/A Compressed data No Encrypted data Yes Cluster database support No Replication support Yes Foreign key support No Backup / point-in-time recover Yes Query cache support Yes Update statistics for data dictionary YesCreating MEMORY tables:
Use CREATE TABLE statement to create am MEMORY table with ENGINE clause. As of MySQL 5.6, it is necessary to use ENGINE clause to specify the MEMORY storage engine because InnoDB is the default engine. The following example shows how to create and use a MEMORY table :
mysql> SELECT * FROM hr.departments; +---------------+----------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+----------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | | 110 | Accounting | 205 | 1700 | | 120 | Treasury | 0 | 1700 | |- - - - - - - -|- - - - - - - - - - - |- - - - - - |- - - - - - -| |- - - - - - - -|- - - - - - - - - - - |- - - - - - |- - - - - - -| +---------------+----------------------+------------+-------------+ 27 rows in set (0.01 sec) mysql> CREATE TABLE test7 ENGINE = MEMORY SELECT * FROM hr.departments; Query OK, 27 rows affected (0.06 sec) Records: 27 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test7 WHERE LOCATION_ID>1700; +---------------+------------------+------------+-------------+ | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | +---------------+------------------+------------+-------------+ | 20 | Marketing | 201 | 1800 | | 40 | Human Resources | 203 | 2400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | +---------------+------------------+------------+-------------+ 4 rows in set (0.00 sec)
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to 'tutorial' database).
mysql> SHOW TABLE STATUS FROM tutorial; +--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | | | table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | | | test7 | MEMORY | 10 | Fixed | 27 | 39 | 59400 | 16357770 | 0 | 0 | NULL | 2014-02-17 11:06:46 | NULL | NULL | utf8_general_ci | NULL | | | +--------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 3 rows in set (0.00 sec)
Remove a MEMORY table:
mysql> DROP TABLE TEST7;
Query OK, 0 rows affected (0.00 sec)
Indexes : The MEMORY storage engine supports both HASH and BTREE indexes. Adding a USING clause you can specify one or the other for a given index. See the following examples :
CREATE TABLE test (id INT, INDEX USING HASH (id)) ENGINE = MEMORY; CREATE TABLE test (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
When to Use MEMORY storage engine:
MySQL: MERGE Storage Engine
The MERGE storage engine (also known as MRG_MyISAM) is a collection of identical MyISAM tables (identical column and index information with same order) that can be used as single table. You must have SELECT, DELETE, and UPDATE privileges on the MyISAM tables that you map to a MERGE table.
Creating MERGE tables :
To create a MERGE table, you must specify a UNION=(list-of-tables) option (indicates which MyISAM tables to use) in the CREAE TABLE statement. The following example at first we have created three tables with two rows then merge it into one table use MERGE storage engine :
mysql> CREATE TABLE tabl1 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE TABLE tabl2 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE tabl3 (rollno INT NOT NULL AUTO_INCREMENT PRIMARY KEY, class CHAR(5), student_name CHAR(40)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO tabl1 (class, student_name) VALUES ('V','Steven'), ('V', 'Neena');
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO tabl2 (class, student_name) VALUES ('VI','Lex'), ('VI', 'Alexander');
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO tabl3 (class, student_name) VALUES ('VII','Bruce'), ('VII', 'David');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE allclass (rollno INT NOT NULL, class CHAR(5), student_name CHAR(40)) ENGINE = MERGE UNION = (tabl1, tabl2, tabl3) INSERT_METHOD = LAST;
Query OK, 0 rows affected (0.09 sec)
mysql> select * from allclass;
+--------+-------+--------------+
| rollno | class | student_name |
+--------+-------+--------------+
| 1 | V | Steven |
| 2 | V | Neena |
| 1 | VI | Lex |
| 2 | VI | Alexander |
| 1 | VII | Bruce |
| 2 | VII | David |
+--------+-------+--------------+
6 rows in set (0.00 sec)
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to 'tutorial' database).
mysql> SHOW TABLE STATUS FROM tutorial; +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | allclass | MRG_MYISAM | 10 | Fixed | 6 | 140 | 840 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | tabl1 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL | utf8_general_ci | NULL | | | | tabl2 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL | utf8_general_ci | NULL | | | | tabl3 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL | utf8_general_ci | NULL | | | | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | | | table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | | | test7 | MEMORY | 10 | Fixed | 27 | 39 | 59400 | 16357770 | 0 | 0 | NULL | 2014-02-17 11:06:46 | NULL | NULL | utf8_general_ci | NULL | | | +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 7 rows in set (0.16 sec)
Security issue: If a user has access to MyISAM table, say t1, that user can create a MERGE table m1 that accesses t1. However, if the administrator revokes the user's privileges on t1, the user can continue to access the data of t1 through m1.
MySQL: CSV Storage Engine
The CSV storage engine stores data in text files using comma-separated values format and the CSV storage engine is always compiled into the MySQL server. The server creates a table format file (.frm extension) and a data file (.csv extension) in the database directory when you create a CSV table. Both .frm and .csv files name begins with the table name. The data file is a plain text file and the storage engine saves data in comma-separated values format. The following example shows how to create and use a CSV table :
Sample Output:
mysql> CREATE TABLE color (slno INT NOT NULL, cname CHAR(30) NOT NULL, ccode CHAR(6) NOT NULL) ENGINE = CSV; Query OK, 0 rows affected (0.12 sec) mysql> INSERT INTO color VALUES(1, 'IndianRed', 'CD5C5C'), (2, 'LightCoral', 'F08080'), (3, 'Salmon', 'FA8072'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * from color; +------+------------+--------+ | slno | cname | ccode | +------+------------+--------+ | 1 | IndianRed | CD5C5C | | 2 | LightCoral | F08080 | | 3 | Salmon | FA8072 | +------+------------+--------+ 3 rows in set (0.00 sec)
You can can read, modify the 'color.CSV' file by spreadsheet applications such as Microsoft Excel or StarOffice Calc.
CSV Limitations :
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to 'tutorial' database).
mysql> SHOW TABLE STATUS FROM tutorial; +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | allclass | MRG_MYISAM | 10 | Fixed | 6 | 140 | 840 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | color | CSV | 10 | Fixed | 3 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | tabl1 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL | utf8_general_ci | NULL | | | | tabl2 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL | utf8_general_ci | NULL | | | | tabl3 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL | utf8_general_ci | NULL | | | | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | | | table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | | | test7 | MEMORY | 10 | Fixed | 27 | 39 | 59400 | 16357770 | 0 | 0 | NULL | 2014-02-17 11:06:46 | NULL | NULL | utf8_general_ci | NULL | | | +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 8 rows in set (0.00 sec)
MySQL: ARCHIVE Storage Engine
The ARCHIVE storage engine is used to store large amounts of unindexed data in a very small footprint. The storage engine is included in MySQL binary distributions. To enable this storage engine (if you build MySQL from source), invoke CMake with the -DWITH_ARCHIVE_STORAGE_ENGINE option. When you create an ARCHIVE table, the server creates a table format file (.frm extension) in the database directory.
Features of ARCHIVE storage engine:
Storage limits None Transactions No Locking granularity Table MVCC No Geospatial data type support Yes Geospatial indexing support No B-tree indexes No T-tree indexes No Hash indexes No Full-text search indexes No Clustered indexes No Data caches No Index caches No Compressed data Yes Encrypted data Yes Cluster database support No Replication support Yes Foreign key support No Backup / point-in-time recovery Yes Query cache support Yes Update statistics for data dictionary YesARCHIVE storage engine supports
ARCHIVE storage engine does not support
ARCHIVE storage engine: Storage & Retrieval
MySQL: EXAMPLE Storage Engine
The EXAMPLE storage engine is a stub engine that does nothing and serve as an example in the MySQL source code that clarify how to begin writing new storage engines. To examine the source for the EXAMPLE engine, look in the storage/example directory of a MySQL source distribution. When you create an EXAMPLE table :
To enable the EXAMPLE storage engine if you build MySQL from source, invoke CMake with the -DWITH_EXAMPLE_STORAGE_ENGINE option.
MySQL: BLACKHOLE Storage Engine
The BLACKHOLE storage engine acts as a "black hole" that accepts data but returns an empty result. To enable the BLACKHOLE storage engine (in case of MySQL build from source), invoke CMake with the -DWITH_BLACKHOLE_STORAGE_ENGINE option. When you create a BLACKHOLE table, the server creates a table format file (.frm) in the database directory. The BLACKHOLE storage engine supports all kinds of indexes. Here is an example :
mysql> CREATE TABLE test10 (slno INT, message CHAR(40)) ENGINE = BLACKHOLE; Query OK, 0 rows affected (0.16 sec) mysql> INSERT INTO test10 VALUES(1, 'message1'), (2, 'message2'); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test10; Empty set (0.03 sec)
The following SHOW TABLE STATUS statement shows the properties of the tables (belongs to 'tutorial' database).
mysql> SHOW TABLE STATUS FROM tutorial; +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | allclass | MRG_MYISAM | 10 | Fixed | 6 | 140 | 840 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | color | CSV | 10 | Fixed | 2 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | tabl1 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:33:52 | 2014-02-17 14:42:21 | NULL | utf8_general_ci | NULL | | | | tabl2 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:01 | 2014-02-17 14:43:09 | NULL | utf8_general_ci | NULL | | | | tabl3 | MyISAM | 10 | Fixed | 2 | 140 | 280 | 39406496739491839 | 2048 | 0 | 3 | 2014-02-17 14:34:22 | 2014-02-17 14:43:59 | NULL | utf8_general_ci | NULL | | | | table1 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2014-02-14 13:16:16 | NULL | NULL | utf8_general_ci | NULL | | | | table2 | MyISAM | 10 | Fixed | 0 | 0 | 0 | 26740122787512319 | 1024 | 0 | NULL | 2014-02-14 15:29:18 | 2014-02-14 15:29:18 | NULL | utf8_general_ci | NULL | | | | test10 | BLACKHOLE | 10 | Fixed | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | NULL | utf8_general_ci | NULL | | | | test7 | MEMORY | 10 | Fixed | 0 | 39 | 0 | 16357770 | 0 | 0 | NULL | 2014-02-19 11:42:17 | NULL | NULL | utf8_general_ci | NULL | | | +----------+------------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 9 rows in set (1.05 sec)
MySQL: FEDERATED Storage Engine
The FEDERATED storage engine is used to access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables. To include the FEDERATED storage engine (in case of MySQL build from source), invoke CMake with the -DWITH_FEDERATED_STORAGE_ ENGINE option.
To enable FEDERATED (not enabled by default in the running server), you must start the MySQL server binary using the --federated option. To check the source for the FEDERATED engine, look in the storage/ federated directory of a MySQL source distribution.
Create a FEDERATED table
You can create a FEDERATED table in the following ways :
Using CONNECTION : To use this method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. Seethe following example :
CREATE TABLE federated_table (
roll_no INT(3) NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(42) NOT NULL DEFAULT '',
total_marks INT(5) NOT NULL DEFAULT '0',
PRIMARY KEY (roll_no),
INDEX stu_name (stu_name),
INDEX total_marks (total_marks)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://feduser@remote_host:9306/federated/test10_table';
The format of the connection string is as follows :
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
Where:
Using CREATE SERVER: To use this method, you must specify the CONNECTION string after the engine type in a CREATE TABLE statement. See the following example:
CREATE SERVER server_name FOREIGN DATA WRAPPER wrapper_name OPTIONS (option [, option] ...)
The server_name is used in the connection string when creating a new FEDERATED table.
Differences between InnoDB and MyISAM
Feature InnoDB MyISAM Storage limits 64TB 256TB Transactions Yes No Locking granularity Row Table MVCC Yes No Geospatial data type support Yes Yes Geospatial indexing support No Yes B-tree indexes Yes Yes T-tree indexes No No Hash indexes No No Full-text search indexes Yes Yes Clustered indexes Yes No Data caches Yes No Index caches Yes Yes Compressed data Yes Yes Encrypted data Yes Yes Cluster database support No No Replication support Yes Yes Foreign key support Yes No Backup / point-in-time recovery Yes Yes Query cache support Yes Yes Update statistics for data dictionary Yes YesPrevious: MySQL Java Connector
Next: MySQL Language Structure
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4