Check Using Query
This process will work in all cases, whether you are accessing your database through the command line or using any GUI tool (like MySQL Workbench or HeidiSQL).
Because the size-related information is already saved in a specific MySQL table. Check the following table:
| Database Name | information_schema |
| Table Name | TABLES |
By checking the table named “TABLES” from the database “information_schema” you will find all the information related to the sizes of the tables.
There are lots of information in that table. But I would like to point out a few important columns:
| TABLE_SCHEMA | Name of the Database |
| TABLE_NAME | Name of the Table |
| TABLE_ROWS | Number of rows in the table |
| DATA_LENGTH | Size of total data for that table (in Byte) |
| INDEX_LENGTH | Size of the total index for that table (in Byte) |
You can check the data directly from the table. Just remember that the size/length is in Byte, so make sure to convert that to make it more understandable.
Or you can use the following queries to get the summary easily.
Check Table Size
If you want to get the size of the tables of a database, then use the following query:
(Make sure to use your database name in place of ‘your_db_name‘)
SELECT
TABLE_SCHEMA AS "DB Name",
TABLE_NAME AS "Table Name",
TABLE_ROWS AS "Number of Rows",
AVG_ROW_LENGTH AS "Avg Row Length",
ROUND(
DATA_LENGTH / POWER(1024, 2),
2
) AS "Data Size (MB)",
ROUND(
INDEX_LENGTH / POWER(1024, 2),
2
) AS "Index Size (MB)",
ROUND(
(DATA_LENGTH + INDEX_LENGTH) / POWER(1024, 2),
2
) AS "Total Table Data Size (MB)"
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = "your_db_name"
ORDER BY
`Total Table Data Size (MB)` DESC;Running this query will show the result like below:
| DB Name | Table Name | Number of Rows | Avg Row Length | Data Size (MB) | Index Size (MB) | Total Table Data Size (MB) |
| your_db_name | timeline_data | 301075170 | 56 | 16164 | 12402 | 28566 |
| your_db_name | staff_details | 27778212 | 67 | 1797 | 5384.97 | 7181.97 |
| your_db_name | location_data | 5086929 | 93 | 452 | 481.02 | 933.02 |
| your_db_name | chain_data | 2322605 | 68 | 150.69 | 398.03 | 548.72 |
| your_db_name | employee_data | 2818956 | 63 | 170.72 | 226.06 | 396.78 |
| your_db_name | staffs | 219555 | 141 | 29.56 | 58.64 | 88.2 |
| your_db_name | build_details | 107190 | 63 | 6.52 | 9.03 | 15.55 |
Check Database Size
If you want to get the total sizes of each database in the server, use the following query:
SELECT
TABLE_SCHEMA AS "DB Name",
ROUND(
SUM(DATA_LENGTH) / POWER(1024, 2),
2
) AS "Data Size (MB)",
ROUND(
SUM(INDEX_LENGTH) / POWER(1024, 2),
2
) AS "Index Size (MB)",
ROUND(
SUM(DATA_LENGTH + INDEX_LENGTH) / POWER(1024, 2),
2
) AS "Total Database Size (MB)"
FROM
information_schema.TABLES
GROUP BY
TABLE_SCHEMA
ORDER BY
`Total Database Size (MB)` DESC;Running this query will show the result like below:
| DB Name | Data Size (MB) | Index Size (MB) | Total Database Size (MB) |
| testdb | 18874.56 | 19066.27 | 37940.83 |
| testdb_demo | 142.33 | 117.33 | 259.66 |
| testdb_pc | 6.59 | 3.42 | 10.02 |
| mysql | 3.25 | 0.30 | 3.55 |
| testdb_test | 0.34 | 0.62 | 0.97 |
| testdb_new | 0.33 | 0.16 | 0.48 |
| sys | 0.02 | 0.00 | 0.02 |
| information_schema | 0.00 | 0.00 | 0.00 |
| performance_schema | 0.00 | 0.00 | 0.00 |
Check using GUI
MySQL WorkBench
Right-click on the database name and select the option “Schema Inspector” (or you can click on the info icon on the right side of database name).
In the new view check the following 2 tabs:
1. Info – Gives a summary of the database.
2. Tables – Gives all the informion about the tables (including size)
HeidiSQL
This tool already shows the size information for database and tables beside the database/table name. So no additional steps required, if you are using HeidiSQL.
phpMyAdmin
phpMyAdmin shows the table size information on the structure view.
