Size per Table information with MySQL

Knowing the size of your data is of course helpful.  The tools have become easier over the years and different versions of MySQL but it is something you should be checking regardless of your MySQL version.

If you are running an old version of MySQL (before information_schema) then you can still gather this data by using "Show table status and add the Data_length to the index_length." The information_schema makes this much easier but you are free to use them whenever you like.

Take advantage of the pager command to gather just the information you are after.

[world]> pager egrep -h "Data_length|Index_length"
PAGER set to 'egrep -h "Data_length|Index_length"'



Use the show table status command to gather the related information:

[world]> show table status like 'City'\G
Data_length: 409600
Index_length: 131072
1 row in set (0.00 sec)



Reset the pager:

[world]> pager
Default pager wasn't set, using stdout.


Table Size = Data_length + Index_length

[world]> select 409600 + 131072 as Table_Size;
+------------+
| Table_Size |
+------------+
| 540672 |
+------------+


The same information is available via the information_schema:

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,SUM(DATA_LENGTH+INDEX_LENGTH) AS size,SUM(INDEX_LENGTH) AS index_size
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IN ('world') AND TABLE_NAME IN ('City') AND ENGINE IS NOT NULL
GROUP BY TABLE_SCHEMA, TABLE_NAME

TABLE_SCHEMA: world
TABLE_NAME: City
ENGINE: InnoDB
size: 540672
index_size: 131072
1 row in set (0.00 sec)


The point, pay attention and know your data.