MySQL Large Table Index Optimization and Delete Performance Tuning

Background : A MySQL master‑slave instance (XX) experiences nightly SLA alerts due to master‑slave replication delay caused by heavy slow queries on the arrival_record table.

Analysis : Using pt-query-digest on the recent mysql-slow.log reveals that select queries on arrival_record scan millions of rows (up to 56 million) with average execution time of 5 seconds, while delete statements perform full table scans on over 100 million rows, taking hundreds of seconds. The existing composite index IXFK_arrival_record has poor selectivity because the leading column product_id has low cardinality.

Execution plans show the query uses the index only on product_id , resulting in large row scans. Adding a dedicated index on receive_time and redesigning the composite index to start with high‑cardinality columns can dramatically reduce scanned rows.

Optimization Steps :

Drop the ineffective composite index IXFK_arrival_record .

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

Create a single‑column index idx_receive_time on receive_time .

Backup the table using mydumper with parallel compression, then restore with myloader to a test instance. Compare online DDL (using ALTER TABLE … DROP INDEX … ADD INDEX … ) with pt‑osc for index recreation; online DDL completes in ~34 minutes versus ~57 minutes for pt‑osc .

After index changes, both select and delete queries use the idx_receive_time index, reducing scanned rows from millions to a few thousand. However, the delete operation still takes ~77 seconds due to large data volume.

Batch Delete Strategy : Implement small‑batch deletes (e.g., 20 000 rows per iteration) based on primary key ranges or time conditions, looping until no rows remain. This reduces lock contention and latency, eliminating SLA alerts.

Conclusion : For massive tables, monitor not only query latency but also maintenance costs such as DDL duration and delete performance. Choose appropriate DDL methods (online DDL vs. pt‑osc) based on table characteristics, and prefer batch deletions to minimize impact on the primary instance and replication lag.