List 10 largest tables in MySQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-01-25

Table of Contents:


    This query returns list of ten largest (by data size) tables.

    Query

    select table_schema as database_name,
           table_name,
           round( (data_length + index_length) / 1024 / 1024, 2)  as total_size,
           round( (data_length) / 1024 / 1024, 2)  as data_size,
           round( (index_length) / 1024 / 1024, 2)  as index_size
    from information_schema.tables
    where table_schema not in ('information_schema', 'mysql',
                               'performance_schema' ,'sys')
          and table_type = 'BASE TABLE'
          -- and table_schema = 'your database name'
    order by total_size desc
    limit 10;
    

    Columns

    • database_name - table's schema name
    • table_name - table name
    • total_size - total table size
    • data_size - size of table's rows
    • index_size - size of indexes

    Rows

    • One row represents one table
    • Scope of rows: ten tables with the biggest total size
    • Ordered by total, data and index size

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept