Introduction

When a MySQL server starts running low on storage, one of the first questions is which table is consuming the space. MySQL exposes table size metadata through information_schema, and that is usually the fastest way to estimate how much room a table and its indexes occupy.

Querying information_schema.TABLES

The standard way to inspect table size is to query information_schema.TABLES. This metadata view includes the size of row data, the size of indexes, and in many cases the amount of free space already allocated.

1SELECT
2    table_schema,
3    table_name,
4    engine,
5    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
6    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
7    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
8FROM information_schema.TABLES
9WHERE table_schema = 'app_db'
10  AND table_name = 'orders';

data_length represents table data, and index_length represents indexes. Adding them together gives a practical estimate of table size.

Interpreting the Main Columns

The numbers are useful, but they are not all measuring the same thing.

• 'data_length is the bytes used for rows.'
• 'index_length is the bytes used for indexes.'
• 'data_free is space allocated but currently unused.'

A second query helps when you suspect fragmentation or churn from many deletes and updates.

1SELECT
2    table_name,
3    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
4    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
5    ROUND(data_free / 1024 / 1024, 2) AS free_mb
6FROM information_schema.TABLES
7WHERE table_schema = 'app_db'
8  AND table_name = 'orders';

If free_mb is large, the table may have internal free pages that can potentially be reorganized.

Finding the Largest Tables in a Schema

Often the real task is not measuring one table in isolation but ranking the largest tables in a database so you know where to investigate.

1SELECT
2    table_name,
3    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
4    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
5    ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
6FROM information_schema.TABLES
7WHERE table_schema = 'app_db'
8ORDER BY (data_length + index_length) DESC
9LIMIT 10;

This is a good first pass for capacity planning, index review, or archive cleanup.

Storage Engine Behavior Matters

The query works across engines, but the interpretation depends on how the table is stored.

For MyISAM, the reported values tend to map more directly to table files on disk. For InnoDB, especially in modern MySQL, the situation is more nuanced. If innodb_file_per_table is enabled, each table usually has its own tablespace file, so the reported size is easier to reason about. If it is disabled, many tables share a larger tablespace, and reclaiming operating system disk space becomes less direct.

You can confirm the engine quickly:

1SELECT table_name, engine
2FROM information_schema.TABLES
3WHERE table_schema = 'app_db'
4  AND table_name = 'orders';

That check matters before running cleanup operations or promising storage savings.

Comparing MySQL Metadata with Filesystem Usage

Sometimes metadata is not enough. You may want to compare MySQL's estimate with what the operating system reports for the database directory. The metadata tells you how much space MySQL attributes to the table, while filesystem tools show what is actually allocated on disk.

A schema total can help bridge those two views:

1SELECT
2    table_schema,
3    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb
4FROM information_schema.TABLES
5WHERE table_schema = 'app_db'
6GROUP BY table_schema;

If the filesystem size is much larger than the schema total, the difference may come from shared tablespaces, redo logs, temporary files, or other engine-managed storage rather than a single oversized table.

What to Do with the Result

Once you identify a large table, the next question is usually why it is large. Possible reasons include long retention windows, wide rows, too many secondary indexes, or a table that keeps deleted space internally. The measurement query does not fix anything by itself, but it gives you a grounded starting point.

Typical follow-up actions include archiving old rows, dropping unused indexes, partitioning large historical data sets, or running maintenance where appropriate.

Common Pitfalls

A common mistake is treating data_length + index_length as an exact operating system reading. It is best treated as a strong estimate from MySQL metadata.

Another issue is ignoring InnoDB configuration. Developers sometimes run maintenance expecting the filesystem to shrink immediately, but shared tablespaces can keep the allocated file size even after rows are removed.

It is also easy to focus only on row count. A table with fewer rows can still be larger than another table if it contains wide text columns, blobs, or many indexes.

Finally, do not forget partitions. A partitioned table may have storage concentrated in a subset of partitions, and a simple top-level view can hide that detail.

Summary

• Use information_schema.TABLES to estimate the size of a MySQL table.
• Add data_length and index_length for a practical total.
• Check data_free when fragmentation or deleted space is relevant.
• Confirm the storage engine before interpreting the numbers.
• Compare metadata with filesystem usage when the totals do not seem to match.
• Use the result to guide archiving, index review, and storage optimization work.