How to check table size in MySQL or MariaDB

Checking table size in MySQL or MariaDB helps prioritize cleanup, capacity planning, and performance tuning by revealing which objects consume the most storage. Large tables and indexes can quietly dominate disk usage, backups, and restore times, even when the application footprint looks small.

Table size is exposed through metadata in INFORMATION_SCHEMA, especially INFORMATION_SCHEMA.TABLES. The columns data_length and index_length represent the estimated on-disk bytes used by table data and indexes, and combining them provides a practical “total table size” for ranking and reporting.

Values depend on the storage engine and statistics freshness. InnoDB row counts (table_rows) are often estimates, and size numbers can reflect allocated space rather than only used space; data_free can indicate reclaimable space but is not a guarantee. On very large tables, refreshing statistics can add load, and installations using a shared InnoDB system tablespace can make “per-table file size” differ from “allocated bytes attributed to a table.”

1. Open a MySQL or MariaDB client session.

   $ mysql --user=root --password
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   ##### snipped #####
   mysql>

   On some systems, the client binary is named mariadb; the SQL steps remain the same.

2. Select the target database (schema).

   mysql> USE myapp;
   Database changed

   Replace myapp with the schema name that contains the table.

3. Show size and related metadata for a single table.

   mysql> SELECT
       table_schema,
       table_name,
       engine,
       table_rows,
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
       ROUND(data_free / 1024 / 1024, 2) AS free_mb
     FROM information_schema.tables
     WHERE table_schema = DATABASE()
       AND table_name = 'orders';
   +--------------+------------+--------+-----------+---------+----------+----------+---------+
   | table_schema | table_name | engine | table_rows | data_mb | index_mb | total_mb | free_mb |
   +--------------+------------+--------+-----------+---------+----------+----------+---------+
   | myapp        | orders     | InnoDB |    182345 | 256.00  | 64.00    | 320.00   | 0.00    |
   +--------------+------------+--------+-----------+---------+----------+----------+---------+
   1 row in set (0.00 sec)

   Replace orders with the table name; table_rows can be approximate for InnoDB, and free_mb is derived from data_free.

4. List table sizes in the selected schema sorted by total size.

   mysql> SELECT
       table_name,
       engine,
       table_rows,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
     FROM information_schema.tables
     WHERE table_schema = DATABASE()
     ORDER BY (data_length + index_length) DESC;
   +----------------+--------+-----------+----------+
   | table_name     | engine | table_rows | total_mb |
   +----------------+--------+-----------+----------+
   | orders         | InnoDB |    182345 | 320.00   |
   | order_items    | InnoDB |   1023345 | 210.50   |
   | customers      | InnoDB |     25000 | 45.20    |
   ##### snipped #####
   +----------------+--------+-----------+----------+
   45 rows in set (0.01 sec)

5. Calculate the total size of the selected schema by summing table sizes.

   mysql> SELECT
       table_schema AS schema_name,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_mb
     FROM information_schema.tables
     WHERE table_schema = DATABASE()
     GROUP BY table_schema;
   +------------+----------+
   | schema_name | total_mb |
   +------------+----------+
   | myapp       | 1024.75  |
   +------------+----------+
   1 row in set (0.00 sec)

6. Identify the largest tables across non-system schemas.

   mysql> SELECT
       table_schema,
       table_name,
       engine,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
     FROM information_schema.tables
     WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
     ORDER BY (data_length + index_length) DESC
     LIMIT 20;
   +------------+----------+--------+----------+
   | table_schema | table_name | engine | total_mb |
   +------------+----------+--------+----------+
   | analytics   | events    | InnoDB | 5120.75  |
   | myapp       | orders    | InnoDB | 320.00   |
   ##### snipped #####
   +------------+----------+--------+----------+
   20 rows in set (0.02 sec)

7. Refresh statistics for a table when sizes or row counts look stale.

   mysql> ANALYZE TABLE orders;
   +-------------+---------+----------+----------+
   | Table       | Op      | Msg_type | Msg_text |
   +-------------+---------+----------+----------+
   | myapp.orders | analyze | status   | OK       |
   +-------------+---------+----------+----------+
   1 row in set (0.12 sec)

   ANALYZE TABLE can add load on large tables and busy servers; schedule it for low-traffic windows when possible.

8. Confirm the reported byte counts with SHOW TABLE STATUS.

   mysql> SHOW TABLE STATUS LIKE 'orders'\G
   *************************** 1. row ***************************
              Name: orders
            Engine: InnoDB
              Rows: 182345
       Data_length: 268435456
      Index_length: 67108864
         Data_free: 0
    Create_time: 2024-01-02 11:22:33
    Update_time: NULL
   ##### snipped #####

   Data_length and Index_length are bytes; compare them to the INFORMATION_SCHEMA query for consistency.

9. Exit the client session.

   mysql> EXIT
   Bye