Optimizing Large MySQL Tables: Index Tuning, Slow Query Analysis, and Efficient Deletion Strategies

The author, a senior architect, investigates performance problems on a large MySQL instance (approximately 100 million rows) where SLA alerts indicate master‑slave replication lag caused by slow queries and heavy delete operations.

Background and Analysis : Using pt-query-digest --since=148h mysql-slow.log | less , the slow‑query log revealed that the most frequent slow SELECTs target the arrival_record table, scanning millions of rows due to a low‑selectivity composite index IXFK_arrival_record . The DELETE statements also performed full table scans, taking hundreds of seconds.

Execution Plans : Sample EXPLAIN output showed the query using the first column of the composite index (product_id) with poor selectivity, resulting in ~32 million rows examined. The DELETE plan reported type: ALL and no usable index.

Optimization Recommendations :

Drop the existing composite index IXFK_arrival_record .

Create a new composite index idx_sequence_station_no_product_id(product_id, sequence, station_no) .

Add a single‑column index idx_receive_time(receive_time) to improve both SELECT and DELETE filtering.

Testing Procedure : The table was copied to a test instance, indexes were rebuilt, and both online DDL and pt‑osc were used for index changes. Online DDL completed in ~34 minutes, roughly half the time of pt‑osc (~57 minutes).

Backup was performed with mydumper (parallel compression) and restored with myloader , confirming that the rebuilt table size matched the original (≈31 GB) and that fragmentation was eliminated.

Post‑Optimization Results : After applying the new indexes, EXPLAIN for the same SELECTs showed usage of idx_receive_time with row estimates dropping from millions to a few hundred thousand. DELETE statements now use the idx_receive_time index, reducing scanned rows dramatically, though the single large DELETE still takes ~77 seconds.

Further Improvements : Implement batch deletions (e.g., DELETE FROM arrival_record WHERE id < @max_id LIMIT 20000; ) in a loop to avoid long‑running transactions and reduce replication lag. Alternatively, delete data in 10‑minute time windows, which brings execution time down to ~1 second per batch and eliminates SLA alerts.

Conclusion : For massive tables, monitoring both query latency and maintenance cost is essential. Choosing the appropriate DDL method (online DDL vs. pt‑osc) based on table characteristics, redesigning indexes for better selectivity, and using incremental deletions together provide significant performance gains and lower operational risk.