MySQL Table Storage Consumption Information

DBAs frequently use metadata data for analysis, capacity planning, and troubleshooting. In MySQL, there are two databases called information schema and performance schema that provide a wealth of metadata and performance-related information.

While Performance Schema offers information about performance, Information Schema typically includes information about the tables or database objects. In other words, server event performance is the main focus of the Performance Schema. Information Schema, on the other hand, is used to inspect metadata.

This article explains how to query against an information schema to retrieve details about a table, such as its creation and modification dates as well as how much storage it uses.

SELECT
TABLE_SCHEMA as DatabaseName,
TABLE_NAME AS TableName,
TABLE_TYPE AS TableType,
CREATE_TIME AS TableCreatedDate,
UPDATE_TIME AS TableLastUpdated,
ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘MyLocalDB’
ORDER BY (data_length + index_length) DESC;