Course contentsShow
← Software Engineering
Lesson 877 of 1,93327. Database Design in PracticeFree lesson

Index Maintenance and Statistics

Understanding index fragmentation, rebuilding strategies, and how query optimizers use index statistics.

Index Maintenance and Statistics

What you'll learn: How indexes degrade over time, when to rebuild them, and how query optimizers depend on statistics to make smart decisions.

Why Indexes Need Maintenance

Even well-designed indexes don't stay optimal forever. As data gets inserted, updated, and deleted, indexes can become fragmented—like a filing cabinet where papers are scattered across drawers instead of neatly organized. Pages split, gaps appear, and what was once a tightly-packed structure becomes bloated.

Fragmentation comes in two forms:

  • Internal fragmentation: Pages are only partially filled, wasting space
  • External fragmentation: Logical order doesn't match physical order on disk, causing inefficient reads

High fragmentation means more disk I/O for the same queries. A query that once read 10 pages might now read 15 scattered pages.

Rebuilding vs Reorganizing

You have two maintenance strategies:

Reorganize (online, lightweight): Compacts pages within existing structures. Use for moderate fragmentation (10-30%). Like tidying a messy desk without replacing furniture.

Rebuild (intensive, may lock tables): Completely recreates the index from scratch. Use for heavy fragmentation (>30%). Like demolishing and rebuilding the filing cabinet.

The Role of Statistics

Indexes aren't useful if the database doesn't know when to use them. Statistics are metadata about your data: how many rows, distribution of values, histogram of common values.

The query optimizer uses statistics to estimate query costs. Outdated statistics lead to bad execution plans—like using a 10-year-old map to navigate a city that's changed dramatically.

Update statistics regularly, especially after large data changes, to ensure the optimizer chooses the right indexes and join strategies.

Key Takeaway: Regularly monitor index fragmentation and rebuild or reorganize as needed; keep statistics current so the query optimizer makes informed decisions about using your indexes effectively.