When adding indexes to large MySQL tables (containing millions or billions of rows), careful planning is essential to avoid performance degradation or service disruption. Below are optimized approaches and best practices tailored to different operational contexts.

Approach Selection and Priority

Evaluate the necessity of the index first using EXPLAIN to confirm measurable query improvement. Avoid over-indexing, as each index increases write overhead and storage usage. Prefer online operations when possible:

• Use ALGORITHM=INPLACE, LOCK=NONE for MySQL 5.6+.
• Schedule non-online operations during low-traffic windows.

Native Online Index Addition (Recommended)

1. ALGORITHM=INPLACE (MySQL 5.6+)

ALTER TABLE user_records 
ADD INDEX idx_created_at (created_at),
ALGORITHM=INPLACE, LOCK=NONE;

This method builds the index directly on the original table without full data copying. Lock time is minimal—usually under one second during metadata switch. Requires ~1.2x the index size in temporary space. Not all index types (e.g., FULLTEXT) support this algorithm.

2. Simulating Concurrent Index Creation

While MySQL lacks native CONCURRENTLY, tools like pt-online-schema-change emulate lock-free behavior by managing synchronization via triggers or binlog parsing.

Third-Party Tool Altenratives

1. pt-online-schema-change (Percona Toolkit)

pt-online-schema-change \
  --alter "ADD INDEX idx_status (status)" \
  --user=admin --password=secret \
  --host=db-master --port=3306 \
  --execute D=app_db,t=orders

Creates a shadow table, applies the schema change, syncs data via triggers, then swaps tables atomically. Compatible with all MySQL versions but doubles disk usage temporarily and may increase replication lag.

2. gh-ost (GitHub’s Migration Tool)

gh-ost \
  --max-load=Threads_running=30 \
  --critical-load=Threads_running=800 \
  --chunk-size=2000 \
  --alter="ADD INDEX idx_region (region_code)" \
  --assume-rbr \
  --execute

Uses binary log stream instead of triggers for synchronization, reducing primary server load. Ideal for billion-row tables but requires binlog_format=ROW.

Handling Special Scenarios

1. Billion-Row Tables

Break indexing into ranges if supported by application logic:

-- Hypothetical: index by ID range (requires app coordination)
CALL add_index_in_chunks('users', 'idx_last_login', 'last_login', 0, 10000000);

Alternatively, use physical backups: restore a snapshot, add index offline, then re-sync.

2. Master-Slave Replication

Apply index on replica first, promote it to master, then apply on former master (now replica). Minimizes downtime and allows validation before promotion.

3. Partitioned Tables

Apply index per partition to reduce locking scope and memory pressure:

ALTER TABLE logs PARTITION p2024 ADD INDEX idx_source (source_id);

Pre-Execution Preparation

• Backup: Use xtrabackup for consistent point-in-time snapshots.

• Disk Space: Reserve at least 50% of table size for temporary files.

• Configuration Tuning: ``` [mysqld] innodb_online_alter_log_max_size = 2G sort_buffer_size = 512M tmp_table_size = 1G

• Timeout Control: Set reasonable execution limits: ``` SET SESSION max_execution_time = 7200000; -- 2 hours

Monitoring During Execution

• Disk Usage: Watch free space via OS tools (df -h).
• Replication Lag: Monitor SHOW REPLICA STATUS\G for delay spikes.
• Locks: Query performance_schema.metadata_locks for contention.
• Process Status: Track progress with SHOW PROCESSLIST.

Rollback Strategies

• Failure Recovery: MySQL auto-rolls back failed DDL; verify cleanup of temp files.

• Performance Regression: Drop problematic index immediately: ``` DROP INDEX idx_slow_query ON orders;

  
   Or use invisible indexes (MySQL 8.0+) for safe testing: ```
  CREATE INDEX idx_hidden ON products (category) INVISIBLE;
  ALTER TABLE products ALTER INDEX idx_hidden VISIBLE; -- enable after validation
  
  

• Space Bloat: Reclaim space with OPTIMIZE TABLE (briefly locks table).

Summary and Best Practices

Best Practices:

• Test index creation in staging with production-scale data.
• Prefer native online DDL unless constraints require third-party tools.
• For massive tables, use gh-ost or chunked approaches.
• Always validate index effectiveness post-deployment with query benchmarks.
• Maintain rollback scripts and monitor systems actively during execution.