Answer
Frequent updates and deletes leave gaps inside index pages. Use OPTIMIZE TABLE or rebuild indexes to reorganize and compact index pages.
Quick Answer
Index fragmentation in MySQL: InnoDB uses B-Tree indexes. After many random inserts, updates, and deletes, index pages become partially filled (fragmented). This wastes space and slows reads. Fix: OPTIMIZE TABLE (rebuilds table and indexes, needs table lock) or ALTER TABLE ... ENGINE=InnoDB (online rebuild with ALGORITHM=INPLACE). Regular ANALYZE TABLE updates statistics but doesn't defragment.
S
SugharaIQ Editorial Team
Verified Answer
This answer has been peer-reviewed by industry experts holding senior engineering roles to ensure technical accuracy and relevance for modern interview standards.
Want to bookmark, take notes, or join discussions?
Sign in to access all features and personalize your learning experience.
Source: SugharaIQ