MySQL stores large object (LOB) data (e.g. varchar(max), text, image columns) in the so-called separate allocation units.

The query below can help you find how much space is allocated for LOB data.

Query

select
    case when col.data_type in ('blob', 'mediumblob', 'longblob', 
            'text', 'mediumtext', 'longtext')
        then 'LOB data'
        else 'Regular data' end as allocation_type,
    round(sum(data_length + index_length) /
        power(1024, 2), 2) as used_mb,
    round(sum(data_length + index_length + data_free) /
        power(1024, 2), 2) as allocated_mb
from information_schema.tables as tab
inner join information_schema.columns as col 
        on col.table_schema = tab.table_schema
            and col.table_name = tab.table_name
where tab.table_schema = 'your database name' -- put your database name here
    and tab.table_type = 'BASE TABLE'
group by 1
order by 1;

Columns

• allocation_type - type of space allcation:
  • LOB data - space allocated for LOB data columns
  • Regular data - space allocated for other columns
• used_mb - space used in MB
• allocated_mb - space allocated in MB

Rows

• One row: represents one type of allocation: LOB/regular
• Scope of rows: query returns two rows

Sample results

Space allocation for LOB and other data types in the Sakila database (schema):