Like most relational databases, MySQL provides useful metadata about the database itself. While most other databases refer to this information as a catalog, the official MySQL documentation refers to the INFORMATION_SCHEMA metadata as tables.
Regardless of the name, what matters is the information provided by these INFORMATION_SCHEMA tables. Everything from views and user_privilieges to columns and tables can be found in the INFORMATION_SCHEMA. For our purposes were particularly interested in the tables metadata, which we can query to actually extract the size of various tables in the system.
List Table Sizes From a Single Database
As can seen in the official documentation, the INFORMATION_SCHEMA.TABLES table contains around 20 columns, but for the purpose of determining the amount of disk space used by tables, well focus on two columns in particular: DATA_LENGTH and INDEX_LENGTH.
- DATA_LENGTH is the length [or size] of all data in the table [in bytes].
- INDEX_LENGTH is the length [or size] of the index file for the table [also in bytes].
Armed with this information, we can execute a query that will list all tables in a specific database along with the disk space [size] of each. We can even get a bit fancier and convert the normal size values from bytes into something more useful and understandable to most people like megabytes.
In this example using the bookstore database, were combining the DATA_LENGTH and INDEX_LENGTH as bytes, then dividing it by 1024 twice to convert into kilobytes and then megabytes. Our result set will look something like this:
If you dont care about all tables in the database and only want the size of a particular table, you can simply add AND TABLE_NAME = "your_table_name" to the WHERE clause. Here we only want information about the book table:
The results, as expected, are now:
List All Table Sizes From ALL Databases
If youre running into an issue where your database is growing in size but you dont know which table is the culprit, it may be useful to query for the size of all tables within all databases in the entire system. This can be accomplished easily with the following query:
This will return not only the size of the table, but also the table name and parent database it is associated with.