MySQL: Identifying Tables Not Using InnoDB

This blog post aims to spotlight tables within a MySQL database that do not employ the InnoDB storage engine. The essence of the query is to filter out tables that do not utilize the InnoDB engine, thereby enabling a focused analysis or migration plan for database optimization. Below is the query:

Let’s dissect the query to understand its mechanics and objectives better:

1. Column Selection: The query targets three specific columns: table_schema, table_name, and engine. For clarity, table_schema is aliased as database_name. This trio of columns provides a concise yet comprehensive overview of each table’s database association, name, and the storage engine it employs.
2. Data Source: Information is fetched from the information_schema.tables table. This meta-database is a treasure trove of data about all other databases and tables within a MySQL environment. By querying this meta-database, users can gain insights into the structural metadata of their databases.
3. Filters Applied:
   • Engine Filter: The primary condition engine NOT LIKE 'InnoDB' zeroes in on tables that do not use the InnoDB storage engine. This is crucial for identifying tables that might not be benefiting from the features and performance optimizations offered by InnoDB.
   • Table Type Filter: By specifying table_type = 'BASE TABLE', the query ensures that only regular tables are considered, excluding views and other non-table database objects.
   • Schema Exclusion: The query excludes tables from system schemas such as information_schema, sys, performance_schema, and mysql. This focus ensures that the analysis is limited to user-created tables, which are more relevant for most optimization and migration tasks.
4. Sorting: Finally, the results are sorted by table_schema and table_name, facilitating an organized and readable output. This sorting aids in quickly identifying specific tables and their corresponding databases.

Why Focus on InnoDB?

InnoDB is renowned for its ACID compliance, support for foreign keys, and performance efficiency, especially in transaction-heavy applications. However, there are scenarios where other engines like MyISAM or MEMORY might be used for legacy reasons or specific use cases where these engines offer advantages. Identifying tables not using InnoDB can help assess potential optimizations, migrations, or adjustments needed to align with best practices or application requirements.

In conclusion, this MySQL query is a powerful tool for database professionals aiming to optimize their database environments. Identifying tables not leveraging InnoDB opens up avenues for enhancements, ensuring that databases are configured for optimal performance and reliability.