MySQL Index Rebuild and Optimization Tutorial: Complete Performance Recovery for Corrupted and Fragmented Indexes on Linux VPS in 2026

Diagnosing MySQL Index Corruption and Fragmentation Issues

Database performance often degrades silently as indexes become fragmented or corrupted over time. Your queries start running slower. Page load times increase. Users complain about sluggish responses.

Index corruption typically manifests as inconsistent query results, unexpected errors during SELECT operations, or dramatic performance drops. Fragmentation occurs naturally as data gets inserted, updated, and deleted, leaving gaps in index structures.

Before diving into repairs, you need to identify which indexes require attention. MySQL provides several diagnostic tools to assess index health and fragmentation levels.

Checking Index Fragmentation Levels

Connect to your MySQL server and run this query to identify fragmented tables:

SELECT 
  table_schema,
  table_name,
  data_free / (data_length + index_length) * 100 AS fragmentation_percentage
FROM information_schema.tables 
WHERE data_free > 0 
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY fragmentation_percentage DESC;

Tables showing fragmentation above 10% typically benefit from index rebuilding. Critical applications should target fragmentation below 5%.

Identifying Corrupted Indexes

Run CHECK TABLE to detect corruption:

CHECK TABLE your_database.your_table EXTENDED;

Look for messages indicating corruption, missing index files, or structural inconsistencies. The EXTENDED option performs thorough checks but takes longer on large tables.

Preparing Your VPS Environment for Index Maintenance

Index rebuilding requires careful preparation. Large tables can consume significant CPU and I/O resources during the process.

First, check available disk space. Index rebuilds temporarily require additional storage:

df -h /var/lib/mysql

Ensure at least 50% free space for the largest table you plan to rebuild. Index operations create temporary files that can exceed the original table size.

Backup Critical Data

Always backup before major index operations:

mysqldump --single-transaction --routines --triggers \
  your_database > /backup/database_pre_rebuild_$(date +%Y%m%d).sql

The --single-transaction flag ensures consistent backups for InnoDB tables without locking the database.

For hosting environments requiring minimal downtime, HostMyCode Managed VPS provides automated backup verification and rapid restoration capabilities.

MySQL Index Rebuild Methods and Implementation

MySQL offers several approaches for rebuilding indexes, each with specific use cases and performance characteristics.

Online Index Rebuild with ALTER TABLE

For InnoDB tables, online rebuilds minimize application disruption:

ALTER TABLE your_table ENGINE=InnoDB;

This command rebuilds the entire table structure, including all indexes. MySQL 8.0 performs this operation online, allowing concurrent reads and writes during most of the process.

For specific index rebuilds:

ALTER TABLE your_table DROP INDEX index_name;
ALTER TABLE your_table ADD INDEX index_name (column_name);

OPTIMIZE TABLE for MyISAM and Archive Tables

MyISAM tables require OPTIMIZE TABLE for defragmentation:

OPTIMIZE TABLE your_myisam_table;

This operation locks the table during execution. Schedule maintenance windows for MyISAM optimization on production systems.

Handling Large Table Rebuilds

Tables exceeding 100GB require special consideration. Monitor the rebuild progress:

SHOW PROCESSLIST;

Look for ALTER TABLE operations and their current stage. Enable the performance_schema to track detailed progress:

SELECT 
  EVENT_NAME,
  WORK_COMPLETED,
  WORK_ESTIMATED,
  ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 2) AS percent_complete
FROM performance_schema.events_stages_current;

Automating MySQL Index Maintenance Tasks

Regular maintenance prevents severe fragmentation and performance degradation. Automated scripts can handle routine optimization during low-traffic periods.

Create a maintenance script that checks fragmentation and rebuilds indexes when necessary:

#!/bin/bash

# MySQL index maintenance script
MYSQL_USER="maintenance_user"
MYSQL_PASS="secure_password"
FRAG_THRESHOLD=10

# Get fragmented tables
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT CONCAT(table_schema, '.', table_name) as full_table_name,
       data_free / (data_length + index_length) * 100 AS fragmentation
FROM information_schema.tables 
WHERE data_free > 0 
  AND table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
  AND data_free / (data_length + index_length) * 100 > $FRAG_THRESHOLD;
" | while read table fragmentation; do
    if [ "$table" != "full_table_name" ]; then
        echo "Optimizing $table (${fragmentation}% fragmented)"
        mysql -u$MYSQL_USER -p$MYSQL_PASS -e "OPTIMIZE TABLE $table;"
    fi
done

Schedule this script via cron for weekly execution:

0 2 * * 0 /usr/local/bin/mysql_maintenance.sh >> /var/log/mysql_maintenance.log 2>&1

Monitoring Index Rebuild Performance

Track key metrics during index operations:

SHOW STATUS LIKE 'Handler_%';
SHOW STATUS LIKE 'Created_tmp_%';
SHOW STATUS LIKE 'Sort_%';

High Handler_read_rnd_next values indicate inefficient index usage. Created_tmp_disk_tables shows when sorts exceed memory limits.

For comprehensive database performance monitoring, our MySQL Performance Monitoring Tutorial covers advanced tracking techniques and alerting strategies.

Advanced Index Optimization Techniques

Beyond basic rebuilds, several advanced techniques can dramatically improve index performance and reduce maintenance overhead.

Composite Index Optimization

Analyze query patterns to create efficient composite indexes:

-- Before: Two separate indexes
ALTER TABLE orders ADD INDEX idx_customer (customer_id);
ALTER TABLE orders ADD INDEX idx_date (order_date);

-- After: One composite index
ALTER TABLE orders DROP INDEX idx_customer, DROP INDEX idx_date;
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);

Composite indexes serve multiple query patterns while reducing storage overhead.

Prefix Index Implementation

Large text columns benefit from prefix indexing:

-- Analyze prefix selectivity
SELECT 
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS prefix_selectivity,
  COUNT(DISTINCT email) / COUNT(*) AS full_selectivity
FROM users;

-- Create prefix index if selectivity is sufficient
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

Aim for prefix selectivity above 0.9 for optimal performance.

Invisible Index Testing

MySQL 8.0 supports invisible indexes for safe testing:

-- Create invisible index for testing
ALTER TABLE products ADD INDEX idx_category_invisible (category_id) INVISIBLE;

-- Make visible after performance verification
ALTER TABLE products ALTER INDEX idx_category_invisible VISIBLE;

This approach prevents query optimizer from using the index until explicitly enabled.

Troubleshooting Common Index Rebuild Issues

Index rebuilds can encounter various problems, especially on busy production systems or resource-constrained VPS environments.

Insufficient Disk Space Errors

When rebuilds fail due to disk space, check temporary directory usage:

SHOW VARIABLES LIKE 'tmpdir';
df -h /tmp

Configure MySQL to use a different temporary directory with more space:

# In my.cnf
[mysqld]
tmpdir = /var/lib/mysql/tmp

Create the directory with proper ownership:

mkdir -p /var/lib/mysql/tmp
chown mysql:mysql /var/lib/mysql/tmp
chmod 750 /var/lib/mysql/tmp

Lock Wait Timeouts

Online operations can still encounter locks. Increase timeout values temporarily:

SET SESSION lock_wait_timeout = 600;
SET SESSION innodb_lock_wait_timeout = 600;

Monitor active transactions that might block the rebuild:

SELECT 
  trx_id,
  trx_started,
  trx_query,
  trx_state
FROM information_schema.innodb_trx
ORDER BY trx_started;

Memory Exhaustion During Large Sorts

Increase sort buffer for complex index operations:

SET SESSION sort_buffer_size = 2097152;  -- 2MB
SET SESSION myisam_sort_buffer_size = 67108864;  -- 64MB for MyISAM

Monitor sort operations with:

SHOW STATUS LIKE 'Sort_%';

For VPS hosting environments requiring guaranteed resources during maintenance operations, HostMyCode VPS hosting provides dedicated CPU and memory allocation that prevents resource contention during database maintenance.

Performance Verification After Index Rebuilds

Rebuilding indexes should result in measurable performance improvements. Proper verification ensures the maintenance effort achieved its goals.

Compare query execution plans before and after optimization:

EXPLAIN FORMAT=JSON SELECT * FROM orders 
WHERE customer_id = 12345 AND order_date > '2026-01-01';

Look for reduced "rows_examined_per_scan" values and "cost_info" improvements in the execution plan.

Benchmark Query Performance

Use MySQL's built-in benchmarking to quantify improvements:

SELECT BENCHMARK(1000, (
  SELECT COUNT(*) FROM orders 
  WHERE customer_id BETWEEN 1000 AND 2000
));

Run identical benchmarks before and after index maintenance to measure performance gains.

Monitor Fragmentation Trends

Track fragmentation over time to establish optimal maintenance intervals:

CREATE TABLE index_fragmentation_log (
  log_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  table_schema VARCHAR(64),
  table_name VARCHAR(64),
  fragmentation_percentage DECIMAL(5,2),
  INDEX idx_date_schema (log_date, table_schema)
);

Automated logging helps identify tables requiring frequent maintenance and optimize rebuild schedules.

Our comprehensive guide on MySQL Query Performance Analysis provides detailed techniques for measuring optimization impact and identifying performance bottlenecks.

Frequently Asked Questions

How often should I rebuild MySQL indexes?

Most production databases benefit from monthly fragmentation analysis and quarterly index rebuilds for heavily updated tables. Tables with frequent INSERT/UPDATE/DELETE operations may require weekly attention, while read-mostly tables can go 6-12 months between optimizations.

Can I rebuild indexes on a live production database?

Yes, MySQL 8.0 supports online index rebuilds for InnoDB tables using ALTER TABLE ENGINE=InnoDB. The operation allows concurrent reads and writes during most of the process, though brief exclusive locks occur at the beginning and end.

What's the difference between OPTIMIZE TABLE and ALTER TABLE ENGINE=InnoDB?

OPTIMIZE TABLE works primarily on MyISAM tables and performs defragmentation. ALTER TABLE ENGINE=InnoDB rebuilds the entire table structure including indexes and is the preferred method for InnoDB tables in MySQL 8.0.

How much disk space do I need for index rebuilds?

Plan for at least 50% additional free space beyond your largest table size. Index rebuilds create temporary copies of table data and indexes, which can temporarily double storage requirements for very large tables.

Should I drop and recreate indexes or use ALTER TABLE?

For InnoDB tables, ALTER TABLE ENGINE=InnoDB is more efficient as it rebuilds the entire table structure in one operation. Individual DROP/ADD INDEX operations require separate table rebuilds and take longer overall.