How to Debug Queries in MySQL

What is the fastest way to see why a MySQL query is slow?

Run EXPLAIN or EXPLAIN ANALYZE before your query to view the execution plan, index usage, and row estimates in under a second.

How do I read the EXPLAIN output?

Focus on type (access method), rows (estimated row count), and Extra (notes like "Using filesort"). High row counts or full table scans signal missing indexes.

Can I profile runtime hotspots?

Enable session profiling, then execute the query.SHOW PROFILE lists stages (parse, send data) with microsecond timing so you can spot bottlenecks.

How do I capture actual latency per step?

MySQL 89 provides EXPLAIN ANALYZE, which returns execution time for each plan node. Compare time-consuming steps with their estimated cost to validate the planner.

Where do I find lock-related slowdowns?

Query information_schema.innodb_trx and performance_schema.metadata_locks for blocking sessions.Investigate long SLEEP or LOCK_WAIT events.

How can I log only slow queries?

Set long_query_time = 1 and enable the slow_query_log. MySQL writes queries exceeding one second to a separate log file for later analysis.

Is there a way to test indexes without creating them?

Use EXPLAIN FORMAT=JSON; the potential_keys array lists candidate indexes.You can also create indexes invisibly (CREATE INDEX idx ON Orders(order_date) INVISIBLE) to test plans without affecting production reads.

Best practices for debugging queries

1) Narrow your SELECT list to needed columns. 2) Filter early with sargable predicates. 3) Compare schema stats with ANALYZE TABLE before benchmarking. 4) Test on production-like data volumes.

.