MySQL database Pluggable Storage Engine components that manage different table operation. There are two type of storage engines in MySQL Server,
1. Transaction-safe tables (TSTs)
2. Non-transaction-safe tables (NTSTs)
The default store engine is InnoDB as of MySQL 5.5 and later version, i have shared 10 types of mysql storage engine queries as below,
1. MySql provided different types of storage engine for manage database.
# mysql
mysql> SHOW ENGINES;
+------------+---------+
| Engine | Support |
+------------+---------+
| InnoDB | YES |
| MRG_MYISAM | YES |
| BLACKHOLE | YES |
| CSV | YES |
| MEMORY | YES |
| FEDERATED | NO |
| ARCHIVE | YES |
| MyISAM | DEFAULT |
+------------+---------+
8 rows in set (0.00 sec)
or
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
8 rows in set (0.00 sec)
Different level of support for the storage engine, as shown in the following table.
Value Meaning
------------------------------------
YES The engine is supported and is active
DEFAULT Like YES, plus this is the default engine
NO The engine is not supported
DISABLED The engine is supported but has been disabled
A value of NO means that the server was compiled without support for the engine, so it cannot be activated at runtime.
2. How to find out default Storage engine MySQL
mysql> SHOW GLOBAL VARIABLES LIKE 'storage_engine';
+----------------+--------+
| Variable_name | Value |
+----------------+--------+
| storage_engine | MyISAM |
+----------------+--------+
1 row in set (0.00 sec)
3. To find out which storage engine using the databases and count number of tables.
mysql> SELECT engine,GROUP_CONCAT(DISTINCT TABLE_SCHEMA) Table_Schema_List,COUNT(*) FROM information_schema.tables GROUP BY engine;
+--------+---------------------+----------+
| engine | Table_Schema_List | COUNT(*) |
+--------+--------------------------------+
| CSV | mysql | 2 |
| InnoDB | roundcube,horde | 72 |
| MEMORY | information_schema | 25 |
| MyISAM | cphulkd,horde,mysql | 92 |
+--------+---------------------+----------+
4. To show specific storage engine for database.
mysql> select table_name,engine from information_schema.tables where table_schema = 'mysql_db' and engine = 'MEMORY';
+-----------------------------+--------+
| table_name | ENGINE |
+-----------------------------+--------+
| db_tokens | MEMORY |
| db_admin | MEMORY |
+-----------------------------+--------+
2 rows in set (0.00 sec)
5. If you want to view the storage engine for all the tables in your database,
mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mysql_db';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| admin | InnoDB |
| comments | InnoDB |
| posts | InnoDB |
| seo_data | InnoDB |
| tagmap | InnoDB |
| tags | InnoDB |
| views | InnoDB |
+------------+--------+
7 rows in set (0.00 sec)
6. How can I check MySQL storage engine type for a specific Table?
mysql> SELECT `ENGINE` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`= 'mysql_db' AND TABLE_NAME = 'posts';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+
1 row in set (0.00 sec)
7. The storage engine is specified at the time of the table creation.
mysql> CREATE TABLE linuxfaq(Id INTEGER PRIMARY KEY, Name VARCHAR(50), id INTEGER) ENGINE='InnoDB';
8. How to changing default storage engine in MySQL:
Open the /etc/my.cnf file and add the below line and restart mysql.
[mysqld]
default-storage-engine = innodb
default-storage-engine = innodb
Once updated the changes in config file need to restart the MySQL.
9. How to change storage engine for particular tables?
mysql > use mysql_db;
mysql> ALTER TABLE gal_modules ENGINE = MyISAM;
Query OK, 9 rows affected (0.08 sec)
Records: 9 Duplicates: 0 Warnings: 0
Once Altered table to view the changed storage engine,
mysql> SELECT `ENGINE` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`= 'mysql_db' AND TABLE_NAME = 'posts';
+--------+
| ENGINE |
+--------+
| MyISAM |
+--------+
10. Disable innodb and set default storage engine to MyISAM ?
Add skip-innodb under [mysqld] in my.cnf and then restart the MySQL server
Open my.cnf file add below two lines under [mysqld] and restart mysql service.
innodb=OFF
default-storage-engine=MyISAM
default-storage-engine=MyISAM
Verify using following query:
SHOW ENGINES;
Comments (0)