A table with several hundred thousand rows is not automatically too large for MySQL. Before moving old records, identify which statements consume time, whether their indexes match the workload, and whether concurrent updates are waiting on locks. Archiving helps only when the removed data is part of the measured bottleneck.
Last updated: October 25, 2026.
SELECT query,
exec_count,
total_latency,
avg_latency,
lock_latency,
rows_examined_avg
FROM sys.statement_analysis
ORDER BY total_latency DESC
LIMIT 10;
EXPLAIN ANALYZE
SELECT transaction_id, status, amount, created_at
FROM transactions
WHERE account_id = 12345
ORDER BY created_at DESC
LIMIT 50;Run the statement summary during a representative busy period. Then use EXPLAIN ANALYZE on a safe SELECT that reproduces the slow path; it executes the statement, so do not apply it casually to data-changing queries.
Measure the workload, not the row count
MySQL documents the sys.statement_analysis view as aggregated statement statistics sorted by total latency. Compare execution count, average latency, lock latency, and rows examined. A query that examines thousands of rows to return a few records usually needs a better access path, while high lock latency points toward transaction design or hot rows.
The slow query log can capture statements exceeding a chosen duration. Enable it for a controlled diagnostic window, protect the log, and summarize repeated query shapes. Check application timing too: connection-pool waits, network calls, and overloaded workers can make a database request appear slow even when SQL execution is brief.
Fix the demonstrated constraint first
The MySQL EXPLAIN documentation shows how plans reveal table order and index use. For a common filter plus sort, a composite index that begins with equality columns and continues with the ordering column may avoid scanning and sorting. Validate the improvement with realistic parameters rather than one convenient account.
During latency spikes, inspect performance_schema.data_lock_waits and active transactions. Keep payment callbacks short, update rows in a consistent order, and commit promptly. Archive only after defining retention, reconciliation, restore, and reporting requirements. Removing old rows may reduce index size and maintenance work, but it will not repair a missing index, a blocked transaction, or exhausted connections. Continue with choosing MySQL indexes, diagnosing connection pressure, and archiving rows without losing history.
