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

Background – A production MySQL instance with a >100 million‑row arrival_record table was experiencing daily SLA alerts due to replication lag caused by slow SELECT and DELETE statements.

Analysis

Used pt-query-digest --since=148h mysql-slow.log to collect slow‑query statistics. The week’s slow‑query log showed 25 403 s total execution time, a maximum query of 266 s, and an average of 5 s per query.

The most frequent slow query was SELECT COUNT(*) FROM arrival_record WHERE product_id=26 AND receive_time BETWEEN '2019‑03‑25 14:00:00' AND '2019‑03‑25 15:00:00' AND receive_spend_ms>=0 , scanning ~32 million rows.

EXPLAIN revealed the query used only the first column ( product_id ) of the composite index IXFK_arrival_record , which has poor selectivity, leading to massive row scans.

The DELETE FROM arrival_record WHERE receive_time < STR_TO_DATE('2019‑02‑23','%Y‑%m‑%d') statement performed a full table scan (type=ALL) and took ~262 s.

Optimization Recommendations

Drop the existing 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(receive_time) to support time‑range queries.

Testing Procedure

Backed up the table with mydumper (parallel compression, 52 s, 1.2 GB output) and restored it using myloader (≈126 m 43 s).

Performed index rebuild using both online DDL and pt‑osc . Online DDL completed in ~34 min, pt‑osc in ~57 min, showing online DDL was roughly twice as fast.

Verified that after adding idx_receive_time , both SELECT and DELETE queries now use the index, reducing scanned rows dramatically (e.g., SELECT rows reduced to ~291 k, DELETE rows to ~7.5 M).

Further Improvements

Replace large‑batch deletions with small‑batch deletes (e.g., DELETE FROM arrival_record WHERE id<@max_id LIMIT 20000 in a loop) to avoid long‑running locks and replication lag.

Alternatively, delete data by time range using the new idx_receive_time index, which reduced a 300 M‑row delete to ~77 s.

Conclusion

When a table grows to tens of gigabytes, it is essential to monitor not only query latency but also maintenance costs such as DDL duration and bulk deletions. Proper index design—especially respecting the left‑most prefix rule—and using online DDL or pt‑osc for schema changes, combined with batch‑wise deletions, can dramatically improve performance and keep replication lag within acceptable limits.