MysqlHow-ToBeginner · 3 min read

How to Check Storage Engine of Table in MySQL

To check the storage engine of a table in MySQL, use the SHOW TABLE STATUS LIKE 'table_name' command and look at the Engine column. Alternatively, query the INFORMATION_SCHEMA.TABLES table filtering by your table name to see the ENGINE used.

📐

Syntax

You can check the storage engine of a table using two main methods:

SHOW TABLE STATUS LIKE 'table_name': Shows detailed info about the table including the storage engine.
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'table_name' AND TABLE_SCHEMA = 'database_name': Queries the system metadata for the engine.

sql

SHOW TABLE STATUS LIKE 'your_table_name';

-- Or using INFORMATION_SCHEMA
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_database_name';

💻

Example

This example shows how to check the storage engine of a table named employees in the database company.

sql

SHOW TABLE STATUS LIKE 'employees';

-- Output will include a column named 'Engine' showing the storage engine used.

-- Alternatively:
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employees' AND TABLE_SCHEMA = 'company';

Output

Engine InnoDB

⚠️

Common Pitfalls

Common mistakes when checking storage engines include:

Not specifying the correct database name when querying INFORMATION_SCHEMA.TABLES, which can return no results.
Using SHOW TABLE STATUS without the LIKE clause, which shows all tables and can be overwhelming.
Confusing the storage engine with other table properties like collation or row format.

sql

/* Wrong: Missing database name, may return no rows */
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employees';

/* Right: Include database name */
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employees' AND TABLE_SCHEMA = 'company';

📊

Quick Reference

Command	Description
SHOW TABLE STATUS LIKE 'table_name';	Shows detailed info including storage engine for the table.
SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'table_name' AND TABLE_SCHEMA = 'database_name';	Queries the storage engine from system metadata.

✅

Key Takeaways

Use SHOW TABLE STATUS LIKE 'table_name' to quickly see the storage engine.

Query INFORMATION_SCHEMA.TABLES with table and database name for precise engine info.

Always specify the database name when querying INFORMATION_SCHEMA to avoid empty results.

Storage engine info is in the 'Engine' column or field in the results.

Avoid confusing storage engine with other table properties like collation.