user3299633 user3299633 - 2 months ago 21
MySQL Question

Grouping size of selected tables in mysql

I'm trying to get the total size of all tables that fit my criteria (latin column where the table collation is not latin, with text or varchar columns), however the sizes I'm getting are not anywhere near the actual sizes.

mysql> select t.table_schema,sum(data_length + index_length)/1024/1024/1024
from tables t
inner join columns c on t.table_schema=c.table_schema and t.table_name=c.table_name
where t.table_schema in ('db1','db2')
and (c.collation_name like '%latin%' or c.character_set_name like '%latin%')
and (c.column_type like 'varchar%' or c.column_type like 'text')
and t.table_collation not like '%latin%'
group by t.table_schema;
+--------------+------------------------------------------------+
| table_schema | sum(data_length + index_length)/1024/1024/1024 |
+--------------+------------------------------------------------+
| db1 | 233.021102905273 |
| db2 | 93.742004394531 |
+--------------+------------------------------------------------+
2 rows in set (0.54 sec)

Answer

You need to join with a subquery that just returns one row per table that matches the column constraints.

select t.table_schema,sum(data_length + index_length)/1024/1024/1024 
from tables t 
inner join (
    SELECT DISTINCT table_schema, table_name
    FROM columns  
    WHERE (c.collation_name like '%latin%' or c.character_set_name like '%latin%') 
    and (c.column_type like 'varchar%' or c.column_type like 'text') 
) c on t.table_schema=c.table_schema and t.table_name=c.table_name
where t.table_schema in ('db1','db2') 
    and  t.table_collation not like '%latin%' 
group by t.table_schema;