Getting the Size of a Table in MySQL

1. Overview

MySQL is a relational database that supports different types of storage engines. We typically determine table sizes to plan for disk capacity, tablespace size, and operating system configuration. Furthermore, “size” can have different connotations such as number of rows, data size, index size, and allocated storage capacity. At the outset, we should remember that when planning for table storage capacity, we’re limited by operating system constraints on file sizes, which are unrelated to MySQL.

We can find the sizes of tables of a MySQL database/schema using one of the methods we’ll discuss in this tutorial.

2. Setting up Tables

To demonstrate, we’ll use tables from the University database. Further, to determine the sizes of tables in the MyISAM storage engine, let’s create a separate set of the same tables that exist in the University database; however, this time by choosing the storage engine as MyISAM. Perhaps, we can create them under a different database name, University_myisam, for example:

CREATE DATABASE University_myisam DEFAULT CHARACTER SET=utf8;  

USE University_myisam;

CREATE TABLE Department
(
    id INT PRIMARY KEY NOT Null,
    name VARCHAR (50),
    code VARCHAR (4),
    UNIQUE (id)
) ENGINE MyISAM;

Alternatively, we can change the storage engine of existing tables.

3. Using the INFORMATION_SCHEMA

We should use the TABLES table in the INFORMATION_SCHEMA to determine the sizes of tables of a MySQL database. Further, we can use size-related columns (bytes units except number of rows) in the TABLES table:

• TABLE_ROWS – Number of rows in a table.
• AVG_ROW_LENGTH – Average row length.
• DATA_LENGTH – Size of the data stored in a table.
• MAX_DATA_LENGTH – Maximum length of the data file in MyISAM. This column isn’t used in InnoDB.
• INDEX_LENGTH – Size of the indexes.
• DATA_FREE – Unused storage capacity.

Let’s run an SQL query to determine the sizes of the tables:

SELECT
  TABLE_NAME AS `TABLE NAME`,
  Engine,
  table_rows AS `Number of Rows`,
  AVG_ROW_LENGTH AS `Average Row Length`,
  FORMAT_BYTES(DATA_LENGTH) AS `Data Length`,
  FORMAT_BYTES(MAX_DATA_LENGTH) AS `Maximum Data Length`,
  FORMAT_BYTES(INDEX_LENGTH) AS `Index Length`,
  FORMAT_BYTES(DATA_LENGTH + INDEX_LENGTH) AS `Total Size`,
  FORMAT_BYTES(DATA_FREE) AS `Data Free`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "university"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Accordingly, the output from the example query converts units to bytes or other as needed:

+---------------+--------+----------------+--------------------+-------------+---------------------+--------------+------------+------------+
| TABLE NAME    | ENGINE | Number of Rows | Average Row Length | Data Length | Maximum Data Length | Index Length | Total Size | Data Free  |
+---------------+--------+----------------+--------------------+-------------+---------------------+--------------+------------+------------+
| teaching      | InnoDB |            612 |                107 | 64.00 KiB   |    0 bytes          | 48.00 KiB    | 112.00 KiB |    0 bytes |
| exam          | InnoDB |            143 |                114 | 16.00 KiB   |    0 bytes          | 48.00 KiB    | 64.00 KiB  |    0 bytes |
| prerequisite  | InnoDB |             50 |                327 | 16.00 KiB   |    0 bytes          | 48.00 KiB    | 64.00 KiB  |    0 bytes |
| registration  | InnoDB |            147 |                111 | 16.00 KiB   |    0 bytes          | 48.00 KiB    | 64.00 KiB  |    0 bytes |
| specification | InnoDB |            137 |                119 | 16.00 KiB   |    0 bytes          | 48.00 KiB    | 64.00 KiB  |    0 bytes |
| course        | InnoDB |             81 |                202 | 16.00 KiB   |    0 bytes          | 32.00 KiB    | 48.00 KiB  |    0 bytes |
| faculty       | InnoDB |             72 |                227 | 16.00 KiB   |    0 bytes          | 32.00 KiB    | 48.00 KiB  |    0 bytes |
| program       | InnoDB |             24 |                682 | 16.00 KiB   |    0 bytes          | 32.00 KiB    | 48.00 KiB  |    0 bytes |
| department    | InnoDB |              5 |               3276 | 16.00 KiB   |    0 bytes          | 16.00 KiB    | 32.00 KiB  |    0 bytes |
| student       | InnoDB |             25 |                655 | 16.00 KiB   |    0 bytes          | 16.00 KiB    | 32.00 KiB  |    0 bytes |
+---------------+--------+----------------+--------------------+-------------+---------------------+--------------+------------+------------+

We should use the built-in FORMAT_BYTES(count) function to format the bytes with suitable units. This is the recommended approach over an oft-used method in which the bytes are divided by 1024, one or more times to convert into suitable units such as KB or MB. An obvious shortcoming of the other method is that we usually don’t know whether table size is in the range of Bytes, KB, MB, or such; therefore, we can’t determine whether to use the 1024 divisor and how many times.

4. Finding Aggregate Tables Size

We can use the MySQL aggregate function called SUM() to find the aggregate tables’ size. Again, let’s use the FORMAT_BYTES(count) function to format the output:

SELECT
    ENGINE,
    SUM(table_rows) 'Total Number Of ROWS',
    FORMAT_BYTES(SUM(data_length)) 'Total Table DATA',
    FORMAT_BYTES(SUM(index_length)) 'Total Index DATA',
    FORMAT_BYTES(SUM(data_length) + SUM(index_length)) 'Total Size'
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA IN ('university')
    GROUP BY engine
    ORDER BY SUM(DATA_length) DESC;

+--------+----------------------+------------------+------------------+------------+
| ENGINE | Total Number Of ROWS | Total Table DATA | Total Index DATA | Total Size |
+--------+----------------------+------------------+------------------+------------+
| InnoDB |                 1296 | 208.00 KiB       | 368.00 KiB       | 576.00 KiB |
+--------+----------------------+------------------+------------------+------------+

We get an aggregated size of all the tables in the University database.

5. Using SHOW TABLE STATUS

We’ve got the option to use the SHOW TABLE STATUS statement to get table size-related information in addition to other table-related information about each non-TEMPORARY table. Let’s demonstrate with an example:

SHOW TABLE STATUS FROM `university` LIKE 'department'\G
*************************** 1. row ***************************
           Name: department
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2025-02-24 16:22:59
    Update_time: 2025-02-24 16:23:43
     Check_time: NULL
      Collation: utf8mb3_general_ci
       Checksum: NULL
 Create_options:
        Comment:

An advantage of using the SHOW TABLE STATUS statement is that the result set has fewer columns. Furthermore, we can use the LIKE clause to match table names using a pattern.

6. Storage Engine Significance

We should remember that the INFORMATION_SCHEMA.TABLES table provides slightly different data depending on whether the storage engine is InnoDB or MyISAM. As an example, the TABLE_ROWS output column stores the exact count for some storage engines, such as MyISAM. This value could differ from the actual number of rows by as much as 40% to 50% for other storage engines, such as InnoDB.

The DATA_LENGTH and the INDEX_LENGTH columns store the length of the data file, and the index file respectively when we use the MyISAM engine. However, they store the approximate amount of space allocated for the clustered index, and the non-clustered indexes respectively when we use the InnoDB storage engine. These values are approximate for the InnoDB engine because it collects the metadata statistics using a very small number of sample pages.

Let’s run the same example SQL query with MyISAM tables to determine the sizes of the tables:

SELECT
  TABLE_NAME AS `TABLE NAME`,
  Engine,
  table_rows AS `Number of Rows`,
  AVG_ROW_LENGTH AS `Average Row Length`,
  FORMAT_BYTES(DATA_LENGTH) AS `Data Length`,
  FORMAT_BYTES(MAX_DATA_LENGTH) AS `Maximum Data Length`,
  FORMAT_BYTES(INDEX_LENGTH) AS `Index Length`,
  FORMAT_BYTES(DATA_LENGTH + INDEX_LENGTH) AS `Total Size`,
  FORMAT_BYTES(DATA_FREE) AS `Data Free`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "university_myisam"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Notably, the output for some columns is quite different with MyISAM even though we used the same SQL scripts to add table data:

+---------------+--------+----------------+--------------------+-------------+---------------------+--------------+------------+------------+
| TABLE NAME    | ENGINE | Number of Rows | Average Row Length | Data Length | Maximum Data Length | Index Length | Total Size | Data Free  |
+---------------+--------+----------------+--------------------+-------------+---------------------+--------------+------------+------------+
| teaching      | MyISAM |            612 |                 38 | 22.80 KiB   | 256.00 TiB          | 33.00 KiB    | 55.80 KiB  |    0 bytes |
| registration  | MyISAM |            147 |                 36 | 5.17 KiB    | 256.00 TiB          | 12.00 KiB    | 17.17 KiB  |    0 bytes |
| exam          | MyISAM |            143 |                 30 | 4.33 KiB    | 256.00 TiB          | 12.00 KiB    | 16.33 KiB  |    0 bytes |
| specification | MyISAM |            137 |                 31 | 4.18 KiB    | 256.00 TiB          | 11.00 KiB    | 15.18 KiB  |    0 bytes |
| course        | MyISAM |             81 |                 85 | 6.80 KiB    | 256.00 TiB          | 4.00 KiB     | 10.80 KiB  |    0 bytes |
| faculty       | MyISAM |             72 |                 58 | 4.12 KiB    | 256.00 TiB          | 4.00 KiB     | 8.12 KiB   |    0 bytes |
| prerequisite  | MyISAM |             50 |                 24 | 1.17 KiB    | 256.00 TiB          | 5.00 KiB     | 6.17 KiB   |    0 bytes |
| program       | MyISAM |             24 |                 69 | 1.63 KiB    | 256.00 TiB          | 4.00 KiB     | 5.63 KiB   |    0 bytes |
| student       | MyISAM |             25 |                 41 | 1.00 KiB    | 256.00 TiB          | 3.00 KiB     | 4.00 KiB   |    0 bytes |
| department    | MyISAM |              5 |                 33 |  168 bytes  | 256.00 TiB          | 3.00 KiB     | 3.16 KiB   |    0 bytes |
+---------------+--------+----------------+--------------------+-------------+---------------------+--------------+------------+------------+

The number of rows output is the same as for InnoDB; however, the difference between MyISAM and InnoDB becomes apparent for very large tables.

7. Conclusion

In this article, we learned about determining the sizes of tables of a MySQL database. We can choose from several columns in the TABLES table in INFORMATION_SCHEMA to obtain table size-related information. However, the data stored for a few of the columns relates to different data attributes depending on the storage engine; InnoDB or MyISAM. Furthermore, we can use the SHOW TABLE STATUS Statement to get table size information.