Find database size allocated for LOB data in MySQL database

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.


    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;


  • 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


  • 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):

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.