How to Find the Engine for a Specific Table in MySQL?

1. Introduction

In this tutorial, we’ll explain how to determine the storage engine used for a table in a MySQL database.

We tested the methods on MySQL version 8.0.37, but they should also work for most other versions. We’ll use our University schema as an example database.

2. What’s an Engine?

A storage engine is an element of a relational database management system (RDBMS) that organizes table data and executes read, write, delete, and update operations on them.

In MySQL, we can choose from various engines that are suitable for different purposes: archiving, testing, ACID-compliant applications, applications that have to guarantee uptime and availability, scattering data across several servers, and other goals.

3. Using the Information Schema

The information schema is a set of tables and views that stores the names of existing tables and views in all the databases on our server. It also keeps track of the constraints, relationships, the names and types of columns, various statistics, etc.

Among other things, the information schema contains information on the engine for each table. We can read that piece of information in the engine column of information_schema.tables:

SELECT engine
FROM information_schema.tables
WHERE table_schema = 'University'
  AND table_name = 'Student';

The table_schema column specifies the database, and table_name specifies the table we’re interested in. Here, the result will show that the Student table in the University database uses InnoDB as its engine.

We can also find the engines of all the tables in a database by slightly modifying the previous query:

SELECT table_name, engine
FROM information_schema.TABLES
WHERE table_schema = 'University'
  AND table_type = 'BASE TABLE'

+ --------------- + ----------- +
| TABLE_NAME      | ENGINE      |
+ --------------- + ----------- +
| Course          | InnoDB      |
| Department      | InnoDB      |
| Department1     | InnoDB      |
| Exam            | InnoDB      |
| Faculty         | InnoDB      |
| Prerequisite    | InnoDB      |
| Product         | InnoDB      |
| Program         | InnoDB      |
| Registration    | InnoDB      |
| Specification   | InnoDB      |
| Student         | InnoDB      |
| Teaching        | InnoDB      |
+ --------------- + ----------- +
12 rows

We use the condition table_type = ‘BASE TABLE’ to filter out views and information_schema tables (so-called system tables).

4. Using the SHOW TABLE STATUS Statement

In MySQL, SHOW statements query the information schema and read server statistics and variables.

The SHOW TABLE STATUS statement is a shortcut for a query that returns columns from information_schema.tables. Among other things, it reveals the engine:

SHOW TABLE STATUS 
FROM University
WHERE name = 'Student'
+ --------- + ----------- + ...
| Name      | Engine      | ...
+ --------- + ----------- + ...
| Student   | InnoDB      | ...
+ --------- + ----------- + ...
1 rows

This statement returns more data than the previous method, as we can’t specify the columns we’re interested in. In addition to the table’s engine, we get various statistics and metadata, such as the number of rows, average row length, and creation time.

Without the WHERE clause, we would get the information on all the tables and views in the specified database. Most columns in the result set contain zeroes and NULL values for views.

5. MySQL Workbench

MySQL Workbench offers a graphical interface for interacting with databases.

We can find the engine used for a table by opening the table inspector (Schemas > University > Tables > right-click Student > Table Inspector):

The engine is specified in the table details along with other information, such as table size estimate, collation, and creation timestamp.

6. Conclusion

In this article, we learned how to find the storage engine used for a specific table or all tables in our database.

We can query the table information_schema.tables or use the SHOW TABLE STATUS statement as a shortcut for a more comprehensive query. Alternatively, we can find the engine using the graphical interface in MySQL Workbench (and other graphical tools for interacting with MySQL databases).