WordPress
MySQL slow queries: capture the statement before adding an index
MySQL slow queries become visible as request latency, worker queues, CPU use, disk activity, or lock waits. The useful workflow captures the exact expensive query and execution plan before adding indexes or increasing database resources.
Capture slow statements during the real workload
Use the slow query log, performance_schema, application profiling, or managed database tooling to identify query text, execution count, duration, rows examined, and timing. A query that takes 400 ms once may matter less than a 40 ms query executed thousands of times.
Protect sensitive query parameters when sharing logs.
Read the execution plan
EXPLAIN can show table scans, index selection, joins, sorting, temporary tables, and estimated rows. Compare the predicates/order/grouping with existing indexes before creating another one.
An index speeds some reads but consumes storage and write/maintenance cost. Avoid overlapping indexes added without evidence.
Check locks and application query patterns
A query can be “slow” because it is waiting for another transaction even when its own execution plan is inexpensive. Inspect lock waits, transaction duration, connection usage, and batch operations.
WordPress plugins can generate N+1 patterns or repeated metadata queries that are better fixed at application level.
Retest representative traffic
After query/index/application changes, compare p95/p99 request latency, database CPU, rows examined, buffer/cache behavior, and write cost. Resize database resources only after the remaining constraint is measured.
- Capture exact slow queries.
- Inspect EXPLAIN.
- Check locks and repetition.
- Retest end-to-end latency.