List the Largest Tables and Disk Usage in MySQL
Ranks tables by data, index and free space size. Core input for capacity planning and archiving decisions.
SELECT table_schema, table_name, engine, table_rows,
ROUND(data_length / 1024 / 1024, 1) AS data_mb,
ROUND(index_length / 1024 / 1024, 1) AS index_mb,
ROUND((data_length + index_length) / 1024 / 1024, 1) AS total_mb,
ROUND(data_free / 1024 / 1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
ORDER BY (data_length + index_length) DESC
LIMIT 25;`DATA_FREE` indicates reclaimable space after heavy DELETE operations; reclaim via `OPTIMIZE TABLE` during low-traffic maintenance windows.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 5.7).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Other MYSQL Diagnostic Scripts
View All (100)Find Long-Running Queries in MySQL
Lists active queries running longer than 60 seconds from the processlist. Fast diagnosis for slowdowns and lock pile-ups.
Find MySQL InnoDB Lock Waits and the Blocking Query
Uses the sys schema to show waiting and blocking sessions with lock modes. Resolves who is holding up whom in a single query.
Detect Unused Indexes in MySQL
Uses sys.schema_unused_indexes to list indexes that were never used. Helps safely drop indexes that slow down writes.
Slowest MySQL Query Digests with Performance Schema
Groups similar queries to show total time, average time and no-index executions. Finds the costliest query patterns without enabling the slow log.