MySQL: Table Size

Sometimes it is required to find out the size of a table in MySQL database.

For example, if it is needed to perform an optimization and you analyze if some tables have become too large and it is time to tweak them somehow.

In this short note i will show the SQL query that gets the information about the required table from the information_schema and prints the table’s size in megabytes.

Cool Tip: Check the size of a database in MySQL with a single query! Read more →

Show The Size Of A Table In MySQL

Run the following command to get the size of a table in megabytes (replace the bold table_name and db_name with the name of the table which size you need to find out and the name of the database where this table is stored):

mysql> SELECT
    table_name AS `Table`,
    round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE table_schema = "db_name"
AND table_name = "table_name";

Sample output:

+------------+------------+
| Table      | Size in MB |
+------------+------------+
| table_name |      31546 |
+------------+------------+
1 row in set (0.17 sec)
Was it useful? Share this post with the world!

Leave a Reply