Calculating MySQL table sizes

Sometimes we need to know how much disk-space a table is using. Or which table is using the most disk-space. Here’s a handy query.

Here’s a handy query which uses the information_schema tables MySQL provides.

It can take MySQL sometime to build the information up required to produce the results.

SELECT
  CONCAT(table_schema, '.', table_name) AS table_name,
  CONCAT(ROUND(data_length / (1024 *1024), 2) , 'M') AS data_length,
  CONCAT(ROUND(index_length / (1024 *1024), 2) , 'M') AS index_length,
  CONCAT(ROUND((data_length + index_length ) / (1024 *1024), 2), 'M') AS total_size,
  CURRENT_DATE AS last_update

FROM information_schema.tables
WHERE table_schema = 'database_name'
ORDER BY ROUND((data_length + index_length) / (1024 *1024), 2) DESC;

Note; Deleting tables and getting a smaller size from the query doesn’t necessarily mean that the disk space will be freed. InnoDB keeps the disk space used and marks as available in it’s table-space.

Tags: mysql