MySQL Large‑Table Index Optimization and Delete Performance Improvement

Background – A master‑slave MySQL instance (one primary, one replica) experiences nightly SLA alerts due to high replication lag caused by slow queries on the large arrival_record table.

Analysis – Using pt‑query‑digest --since=148h mysql‑slow.log | less the author collected slow‑query statistics: total slow‑query time 25,403 s, longest query 266 s, average 5 s, and 40 k+ slow SELECT arrival_record executions. The SELECT statements filter on product_id , receive_time and receive_spend_ms , but only the first column of the composite index IXFK_arrival_record(product_id,station_no,sequence,receive_time,arrival_time) is used, leading to scans of millions of rows.

Execution Plan Inspection – The EXPLAIN SELECT count(*) FROM arrival_record WHERE product_id=26 AND receive_time BETWEEN ... output shows type: ref with rows: 32,261,320 . The index IXFK_arrival_record is not selective because product_id has low cardinality.

Index Optimization Proposal – Drop the composite index IXFK_arrival_record and create a new composite index idx_sequence_station_no_product_id(product_id,sequence,station_no) plus a single‑column index idx_receive_time on receive_time . This allows the optimizer to use the more selective receive_time index.

Delete Statement Issue – The routine DELETE FROM arrival_record WHERE receive_time < STR_TO_DATE('2019-02-23','%Y-%m-%d') performs a full table scan ( type: ALL ) scanning over 109 M rows and taking ~262 s.

Data Capture with tcpdump – Captured SELECT statements via: tcpdump -i bond0 -s 0 -l -w - dst port 3316 | strings | grep select | egrep -i 'arrival_record' > /tmp/select_arri.log and extracted the WHERE clauses with a shell loop.

Backup & Restore – Used mydumper for parallel compressed backup (≈52 s, 1.2 GB) and myloader for parallel import (≈126 min). The restored test table showed no fragmentation and matched the original size.

DDL Execution Comparison – Performed index changes using online DDL and the pt‑osc tool. Online DDL completed in ~34 min, while pt‑osc took ~57 min, making online DDL roughly twice as fast for this workload.

Post‑Optimization Results – After adding idx_receive_time , the same SELECT queries now use a range scan on the new index, reducing scanned rows from millions to a few hundred thousand. The DELETE still scans 3 M rows via the new index and now takes ~77 s.

Batch Delete Strategy – To further reduce impact, the author recommends deleting in small batches (e.g., 20 k rows per transaction) using the primary key order, or deleting by time windows, which brings each batch down to ~1 s.

Conclusion – Large tables require careful index design, consideration of DDL methods, and batch‑processing for maintenance operations to avoid long replication lag and SLA violations.