Overview
In this tutorial, we will learn different ways to determine storage engine information on MySQL database server.
SHOW ENGINES
To know what storage engines available on the MySQL server, use the SHOW ENGINES command. More information on the command:
https://www.testingdocs.com/mysql-show-engines-statement/
Display Storage engine for a table
To determine which storage engine is used for a given table, we can use the following statements
- SHOW CREATE TABLE
- SHOW TABLE STATUS
Examples
Let’s determine the storage engines for the tables in the world MySQL database.
SHOW CREATE TABLE
mysql> SHOW CREATE TABLE City \G
*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `city` (
`ID` int NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT ”,
`CountryCode` char(3) NOT NULL DEFAULT ”,
`District` char(20) NOT NULL DEFAULT ”,
`Population` int NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.03 sec)
SHOW TABLE STATUS
mysql> SHOW TABLE STATUS LIKE ‘Country’ \G
*************************** 1. row ***************************
Name: country
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 239
Avg_row_length: 479
Data_length: 114688
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2022-06-07 20:06:10
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.02 sec)
—
MySQL Tutorials
MySQL Tutorials on this website:
https://www.testingdocs.com/mysql-tutorials-for-beginners/
For more information on MySQL Database: