How to check table size in MySQL or MariaDB

Checking table size in MySQL or MariaDB helps you find the tables that are driving disk growth before backups, replicas, schema changes, or retention jobs start taking longer than expected. A per-table view quickly shows whether the growth is concentrated in one busy table or spread across an entire application database.

Both servers expose table-size metadata through INFORMATION_SCHEMA.TABLES. The DATA_LENGTH and INDEX_LENGTH columns report the space allocated to table data and indexes, while SHOW TABLE STATUS exposes the same core fields for a quick table-by-table cross-check inside the SQL client.

These figures are still estimates rather than a filesystem audit. InnoDB row counts are approximate, DATA_FREE can describe tablespace-level free space rather than bytes one table can immediately reclaim, and MySQL can cache INFORMATION_SCHEMA table statistics for up to @@information_schema_stats_expiry seconds unless you set it to 0 or refresh the table with ANALYZE TABLE.

1. Open a MySQL or MariaDB client session on the target server.

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

   On MariaDB systems the client command is often mariadb instead of mysql, but the SQL queries are the same.

2. Select the database that contains the table.

   mysql> USE appdb;
   Database changed

   Replace appdb with the database you want to inspect so the later queries can use DATABASE().

3. Show the size breakdown for one table from INFORMATION_SCHEMA.TABLES.

   mysql> SELECT
       ->   table_schema AS `Database`,
       ->   table_name AS `Table`,
       ->   engine AS `Engine`,
       ->   table_rows AS `Rows (est.)`,
       ->   ROUND(COALESCE(data_length, 0) / 1024 / 1024, 2) AS `Data (MiB)`,
       ->   ROUND(COALESCE(index_length, 0) / 1024 / 1024, 2) AS `Index (MiB)`,
       ->   ROUND((COALESCE(data_length, 0) + COALESCE(index_length, 0)) / 1024 / 1024, 2) AS `Total (MiB)`,
       ->   ROUND(COALESCE(data_free, 0) / 1024 / 1024, 2) AS `Free (MiB)`
       -> FROM information_schema.TABLES
       -> WHERE table_schema = DATABASE()
       ->   AND table_name = 'orders'
       ->   AND table_type = 'BASE TABLE';
   +----------+--------+--------+-------------+------------+-------------+-------------+------------+
   | Database | Table  | Engine | Rows (est.) | Data (MiB) | Index (MiB) | Total (MiB) | Free (MiB) |
   +----------+--------+--------+-------------+------------+-------------+-------------+------------+
   | appdb    | orders | InnoDB |         500 |       0.14 |        0.03 |        0.17 |       0.00 |
   +----------+--------+--------+-------------+------------+-------------+-------------+------------+
   1 row in set (0.01 sec)

   Replace orders with the table name you want to inspect. The TABLE_TYPE = 'BASE TABLE' filter keeps views out of the size report, and Rows (est.) is approximate for InnoDB.

4. Rank tables in the current database by total allocated size.

   mysql> SELECT
       ->   table_name AS `Table`,
       ->   ROUND(COALESCE(data_length, 0) / 1024 / 1024, 2) AS `Data (MiB)`,
       ->   ROUND(COALESCE(index_length, 0) / 1024 / 1024, 2) AS `Index (MiB)`,
       ->   ROUND((COALESCE(data_length, 0) + COALESCE(index_length, 0)) / 1024 / 1024, 2) AS `Total (MiB)`
       -> FROM information_schema.TABLES
       -> WHERE table_schema = DATABASE()
       ->   AND table_type = 'BASE TABLE'
       -> ORDER BY (COALESCE(data_length, 0) + COALESCE(index_length, 0)) DESC;
   +-----------+------------+-------------+-------------+
   | Table     | Data (MiB) | Index (MiB) | Total (MiB) |
   +-----------+------------+-------------+-------------+
   | audit_log |       0.25 |        0.05 |        0.30 |
   | orders    |       0.14 |        0.03 |        0.17 |
   | customers |       0.02 |        0.02 |        0.03 |
   +-----------+------------+-------------+-------------+
   3 rows in set (0.00 sec)

   This ranking reflects allocated table and index space reported by the storage engine, not every file under the data directory.

5. Find the largest application tables across non-system databases.

   mysql> SELECT
       ->   table_schema AS `Database`,
       ->   table_name AS `Table`,
       ->   ROUND((COALESCE(data_length, 0) + COALESCE(index_length, 0)) / 1024 / 1024, 2) AS `Total (MiB)`
       -> FROM information_schema.TABLES
       -> WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
       ->   AND table_type = 'BASE TABLE'
       -> ORDER BY (COALESCE(data_length, 0) + COALESCE(index_length, 0)) DESC
       -> LIMIT 10;
   +----------+-----------+-------------+
   | Database | Table     | Total (MiB) |
   +----------+-----------+-------------+
   | appdb    | audit_log |        0.30 |
   | appdb    | orders    |        0.17 |
   | appdb    | customers |        0.03 |
   +----------+-----------+-------------+
   3 rows in set (0.00 sec)

   Use this server-wide query when you need to find the biggest application tables without mixing in system schemas.

6. Refresh table metadata before rerunning the size query when the values look stale.

   mysql> SET SESSION information_schema_stats_expiry = 0;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> ANALYZE TABLE orders;
   +--------------+---------+----------+----------+
   | Table        | Op      | Msg_type | Msg_text |
   +--------------+---------+----------+----------+
   | appdb.orders | analyze | status   | OK       |
   +--------------+---------+----------+----------+
   1 row in set (0.01 sec)

   ANALYZE TABLE can add I/O on large or busy tables. Run it during a low-traffic window when possible.

   information_schema_stats_expiry is a MySQL session variable. MariaDB does not use it, but ANALYZE TABLE is still the direct way to refresh table statistics there.

7. Cross-check the reported bytes with SHOW TABLE STATUS.

   mysql> SHOW TABLE STATUS LIKE 'orders'\G
   *************************** 1. row ***************************
              Name: orders
            Engine: InnoDB
           Version: 10
        Row_format: Dynamic
              Rows: 500
    Avg_row_length: 294
       Data_length: 147456
   Max_data_length: 0
      Index_length: 32768
         Data_free: 0
    Auto_increment: 512
       Create_time: 2026-04-09 22:22:23
       Update_time: 2026-04-09 22:22:23
        Check_time: NULL
         Collation: utf8mb4_0900_ai_ci
          Checksum: NULL
    Create_options:
           Comment:
   1 row in set (0.00 sec)

   SHOW TABLE STATUS exposes the same core size fields in bytes. MariaDB may show a few extra trailing columns, but Data_length, Index_length, and Data_free map to the same storage figures.

8. Exit the client session.

   mysql> EXIT
   Bye