how to get mysql table size in GB

Question

Just ended up with calculating the size of MySQL table in GB with the following query.

SELECT (data_length+index_length)/power(1024,3) tablesize_gb FROM information_schema.tables WHERE table_schema='db' and table_name='tablename'

Is it possible to get the size of a MySQL row in GB.

Or how to get the avg row size for the table in GB.

Answer 1

To get the average row length (including overhead), use the AVG_ROW_LENGTH column in the information schema table:

select AVG_ROW_LENGTH from INFORMATION_SCHEMA.tables;

As far as I'm aware, there's no way to calculate the exact actual size of a single, specific row in MySQL.

Answer 2

Hi this might do the trick, we had a similar issue and had to find out which types of rows take up the most space. That's why here with a group by...

SELECT groupval, (sum(length(somefield) + length(someotherfield)) / 1024) / 1024 as "fields_size_mb"
FROM table
GROUP BY groupval
ORDER BY fields_size_mb desc;

Answer 3

SELECT 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024 ), 2) as `Size in MB`,
     round((AVG_ROW_LENGTH / 1024), 2) as `Avg row size in KB`
FROM information_schema.TABLES WHERE table_schema = 'your_db_name'
ORDER BY `Size in MB` DESC