MySQL InnoDB Table Fragmentation and Reclaimable Space

Lists tables with large free space (data_free). Identifies candidates for space reclamation with OPTIMIZE TABLE.

#fragmentation#data_free#optimize

mysql · sql

SELECT table_schema, table_name,
       ROUND(data_length / 1048576, 1) AS data_mb,
       ROUND(data_free   / 1048576, 1) AS free_mb,
       ROUND(100 * data_free / NULLIF(data_length + index_length + data_free, 0), 1) AS free_pct
FROM information_schema.tables
WHERE engine = 'InnoDB' AND data_free > 100 * 1048576
ORDER BY data_free DESC
LIMIT 25;

Operational Notes & Remediation Guidance

Run `OPTIMIZE TABLE` or `ALTER TABLE ... ENGINE=InnoDB` during off-peak maintenance hours to rebuild fragmented tablespaces.

Production Safety & Execution Standards

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)

#21Performance

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.

View Script

#22Locking

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.

View Script

#23Storage

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.

View Script

#24Performance

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.

View Script