Grouping size of selected tables in mysql

Question

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 1

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 (collation_name like '%latin%' or character_set_name like '%latin%') 
    and (column_type like 'varchar%' or 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;