Debugging Slow and Wrong Queries

Slow: find it before fixing it#

Don't guess which query is slow. Measure.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;           -- seconds
SET GLOBAL log_queries_not_using_indexes = OFF;
SHOW VARIABLES LIKE 'slow_query_log_file';

Read the log with mysqldumpslow -s t -t 10 slow.log, which groups similar queries and ranks them by total time. A query that takes 20 ms but runs a million times a day matters more than one 5 second report that runs weekly.

For live inspection:

SHOW FULL PROCESSLIST;
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.schema_unused_indexes;

The sys schema is built on Performance Schema and is the friendliest view into what the server is doing. schema_unused_indexes shows indexes costing you writes for no benefit.

A systematic approach#

1. Reproduce with real parameters
2. EXPLAIN ANALYZE  ▶ where is time spent?
3. Look at rows examined vs rows returned
4. Fix: index, rewrite, or reduce data
5. Re-measure

A big gap between rows examined and rows returned says the engine is filtering after reading, so an index or a better predicate is missing.

Typical fixes: add a composite index matching WHERE then ORDER BY; remove functions on indexed columns; replace deep OFFSET; select fewer columns; turn a correlated subquery into a join; aggregate before joining.

Blocked, not slow#

Sometimes the query is fast but waiting on a lock:

SELECT * FROM sys.innodb_lock_waits\G
SELECT * FROM performance_schema.data_locks;

You'll often find a transaction someone left open. Fix the app, not the database.

Wrong results: shrink the problem#

When a query returns wrong rows or numbers, bisect it:

1. Reduce to the smallest query that still shows the problem.
2. Replace aggregates with the raw rows and look at them.
3. Check each join's cardinality: SELECT COUNT(*) after each join to see where rows multiply.
4. Check for NULLs in join and filter columns.

-- did the join multiply rows?
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM orders o JOIN order_items oi ON oi.order_id = o.id;

Most wrong totals come from join multiplication, NOT IN with NULL, COUNT(*) on a left join, or implicit type and collation conversion.

Warnings are worth reading too:

SHOW WARNINGS;

Truncated values and implicit conversions show up there, not as errors, unless strict mode is on. Keep sql_mode strict.

Common mistakes#

• Optimizing without measuring.
• Adding indexes for a query that runs once a month.
• Debugging a 200-line query instead of shrinking it.
• Ignoring SHOW WARNINGS.

Practice#

Enable the slow log with a threshold of 0 for five minutes while running your earlier exercises, then rank the queries with mysqldumpslow. Take the slowest and apply the five-step routine. Then deliberately write a join that inflates a SUM, and use row-count checks to find where it happens.