How to Check Table Size in MySQL

Galaxy Glossary

How do I check the size of a table in MySQL?

Use INFORMATION_SCHEMA or SHOW TABLE STATUS to quickly find how much disk space each MySQL table uses.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What SQL query gives a tables size in MB?

Query information_schema.tables because it stores data_length and index_length. Summing these columns delivers the total on-disk footprint in bytes, which you can convert to megabytes for readability.

SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables WHERE table_schema = 'ecommerce' AND table_name = 'Orders';

How do I list every table with its size?

Remove the table_name filter and order by the calculated column. This reveals the largest space consumers instantly.

SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables WHERE table_schema = 'ecommerce' ORDER BY total_mb DESC;

Why use SHOW TABLE STATUS?

SHOW TABLE STATUS offers the same numbers plus row counts and engine details. Its convenient inside interactive sessions, although it produces slightly more verbose output.

SHOW TABLE STATUS FROM ecommerce LIKE 'Orders';

Can I check database-wide size?

Aggregate by schema to understand storage at the database level and guide capacity planning.

SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS db_mb FROM information_schema.tables GROUP BY table_schema;

Best practices for accurate readings

Run ANALYZE TABLE before measuring for up-to-date statistics, exclude temporary tables when summing sizes, and monitor growth with scheduled snapshots.

Why How to Check Table Size in MySQL is important

How to Check Table Size in MySQL Example Usage


-- Find the five largest tables in the ecommerce database
SELECT table_name,
       ROUND((data_length + index_length)/1024/1024,2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'ecommerce'
ORDER BY total_mb DESC
LIMIT 5;

How to Check Table Size in MySQL Syntax


SELECT table_name,
       data_length,       -- bytes used by data
       index_length,      -- bytes used by indexes
       data_free,         -- free space
       ROUND((data_length + index_length)/1024/1024,2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'ecommerce'          -- database name
  AND table_name   = 'Orders';            -- optional table filter

Common Mistakes

Mistake: Omitting the WHERE clause for table_schema, causing results from all databases. Fix: Always filter by the intended schema or use a USE statement first.
Mistake: Confusing row counts with storage size. A small row count table can still be large if it has wide columns or large indexes. Always rely on data_length + index_length.

Frequently Asked Questions (FAQs)

Does INFORMATION_SCHEMA include InnoDB free space?

Yes. The data_free column reports allocated but unused space within the tables tablespace.

Will compressing tables reduce total_mb immediately?

After converting to ROW_FORMAT=COMPRESSED or using OPTIMIZE TABLE, MySQL reclaims space and youll see a lower total_mb.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie