MSK Institute • Shikohabad
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Connecting serverPlease wait...
💻 Practical Labs🚀 Free Demo📜 Verified Certificates
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Over time, heavy transactional activity—especially frequent UPDATE statements on variable-length text/JSON and frequent DELETE operations—leaves tables and indexes fragmented.
Fragmented tables waste disk storage, bloat the buffer pool with empty space, and slow down full scans. MySQL provides three essential maintenance commands: CHECK TABLE, ANALYZE TABLE, and OPTIMIZE TABLE.
CHECK TABLE validates table integrity, verifying that index keys match row data and internal page structures are uncorrupted:
If corruption is discovered, InnoDB crash recovery or restore from backup is typically required.
The MySQL Query Optimizer relies on index cardinality statistics (estimates of unique values in each column) to choose between index lookups and table scans.
If a table undergoes massive data shifts (e.g., after loading 5 million new rows), statistics can become stale, causing the optimizer to make poor index choices:
Note: ANALYZE TABLE runs near-instantaneously by sampling a subset of index pages (governed by innodb_stats_persistent_sample_pages).
When rows are deleted in InnoDB, the disk space is not returned to the operating system. Instead, InnoDB marks those 16 KB page slots as "free" for future inserts.
OPTIMIZE TABLE rebuilds the table and indexes from scratch into a fresh contiguous .ibd tablespace file:
In InnoDB, OPTIMIZE TABLE maps directly to:
In modern MySQL 8.0 with InnoDB, OPTIMIZE TABLE utilizes Online DDL:
SELECT, INSERT, UPDATE, and DELETE statements throughout the rebuild!Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
Test your understanding of this lesson with 5 questions. Each question has one correct answer.