In MySQL, you can check the size of all tables within a given database (or on all databases) by querying the information_schema.tables table. This table stores data about each table in a database, including information about each table’s size, creation date, collation, etc.

You can also find the size of each table within a database by using the MySQL Workbench GUI.

This article provides a quick overview of each method.

Code Example

Here’s an example of a SQL query that returns the size of the tables within a database.

SELECT 
    table_name 'Table Name',
    data_length + index_length 'Size in Bytes',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) 'Size in MiB'
FROM information_schema.tables
WHERE table_schema = 'sakila'
ORDER BY (data_length + index_length) DESC;

Result:

+----------------------------+---------------+-------------+
| Table Name                 | Size in Bytes | Size in MiB |
+----------------------------+---------------+-------------+
| rental                     |       2785280 |        2.66 |
| payment                    |       2228224 |        2.13 |
| inventory                  |        376832 |        0.36 |
| film                       |        278528 |        0.27 |
| film_actor                 |        278528 |        0.27 |
| film_text                  |        196608 |        0.19 |
| customer                   |        131072 |        0.13 |
| address                    |        114688 |        0.11 |
| staff                      |         98304 |        0.09 |
| film_category              |         81920 |        0.08 |
| city                       |         65536 |        0.06 |
| store                      |         49152 |        0.05 |
| actor                      |         32768 |        0.03 |
| country                    |         16384 |        0.02 |
| language                   |         16384 |        0.02 |
| category                   |         16384 |        0.02 |
| staff_list                 |             0 |        0.00 |
| customer_list              |             0 |        0.00 |
| nicer_but_slower_film_list |             0 |        0.00 |
| actor_info                 |             0 |        0.00 |
| sales_by_film_category     |             0 |        0.00 |
| film_list                  |             0 |        0.00 |
| sales_by_store             |             0 |        0.00 |
+----------------------------+---------------+-------------+

This returns the size of each table in the sakila database. Replace sakila with the name of the relevant database. Of course, you could omit the WHERE clause altogether, and this will return the table size of all tables on all databases, but it will be a long list.

In this case, I’ve ordered the results by table size in descending order. I’ve also added a column with the size converted to MiB (Mebibytes).

Another method of converting the size is to use the FORMAT_BYTES() function.

The FORMAT_BYTES() Function

You can use the FORMAT_BYTES() function to convert the size from bytes into either bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), PiB (pebibytes), or EiB (exbibytes), with the units indicator added to the value. The units indicator is added automatically. The function will decide which unit to convert into, depending on the size of the value, so you don’t have any control over which unit to use.

Here’s an example of using this method:

SELECT 
    table_name 'Table Name',
    data_length + index_length 'Size in Bytes',
    FORMAT_BYTES((data_length + index_length)) 'Size (Formatted)'
FROM information_schema.tables
WHERE table_schema = 'sakila'
ORDER BY (data_length + index_length) DESC;

Result:

+----------------------------+---------------+------------------+
| Table Name                 | Size in Bytes | Size (Formatted) |
+----------------------------+---------------+------------------+
| rental                     |       2785280 | 2.66 MiB         |
| payment                    |       2228224 | 2.12 MiB         |
| inventory                  |        376832 | 368.00 KiB       |
| film                       |        278528 | 272.00 KiB       |
| film_actor                 |        278528 | 272.00 KiB       |
| film_text                  |        196608 | 192.00 KiB       |
| customer                   |        131072 | 128.00 KiB       |
| address                    |        114688 | 112.00 KiB       |
| staff                      |         98304 | 96.00 KiB        |
| film_category              |         81920 | 80.00 KiB        |
| city                       |         65536 | 64.00 KiB        |
| store                      |         49152 | 48.00 KiB        |
| actor                      |         32768 | 32.00 KiB        |
| category                   |         16384 | 16.00 KiB        |
| country                    |         16384 | 16.00 KiB        |
| language                   |         16384 | 16.00 KiB        |
| actor_info                 |          NULL | NULL             |
| customer_list              |          NULL | NULL             |
| film_list                  |          NULL | NULL             |
| nicer_but_slower_film_list |          NULL | NULL             |
| sales_by_film_category     |          NULL | NULL             |
| sales_by_store             |          NULL | NULL             |
| staff_list                 |          NULL | NULL             |
+----------------------------+---------------+------------------+

MySQL Workbench

If you’re using the MySQL Workbench GUI, you can point and click your way to the table sizes. Here’s how:

1. Navigate to the database in the Schemas pane
2. Hover over the applicable database
3. Click the little information icon beside the database name. This loads information about the database, including its approximate size, table count, collation, etc.
4. Click the Tables tab. This displays information about each table in the database, including the data length and index length.