Slow Query Log Analysis

In this lesson, you will learn how to parse the MySQL slow query log to identify problematic SQL patterns and determine where specific indexes are missing.

The Anatomy of a Slow Query

When your database engine (InnoDB) takes longer than the long_query_time threshold to execute a statement, it records the event in the slow query log. By default, this log is a text file containing the query, execution time, rows examined, and rows sent.

The most critical metric here is the ratio between Rows_examined and Rows_sent. If a query examines 100,000 rows to return only 10, it is a classic candidate for indexing.

Identifying Patterns

Instead of reading raw text files, use tools like mysqldumpslow to aggregate similar queries. It groups queries by pattern, replacing specific data values with placeholders:

# Summarize queries by average execution time
mysqldumpslow -s at /var/lib/mysql/slow-query.log

If you see a recurring pattern like this: SELECT * FROM orders WHERE user_id = N AND status = 'pending';

And the report shows Rows_examined: 50000, the database is likely performing a full table scan because it lacks a composite index. You are forcing the engine to filter through the entire tablespace instead of navigating directly to the relevant pages.

Driving Your Indexing Strategy

When you analyze these logs, look for:

1. High Scans: Queries with Rows_examined significantly higher than Rows_sent. This indicates the query engine is "searching" rather than "finding."
2. Sort/Group Pressure: Look for Sort_merge_passes in the log. If this is high, your query is likely performing a filesort outside of the sort buffer, and an index that supports the ORDER BY clause will eliminate the need for extra memory allocation.
3. Redundancy: If the same table appears in many slow queries with different filters, evaluate whether a single composite index can satisfy all those disparate filter conditions.

By extracting these patterns, you stop "guessing" which columns to index and start building an indexing strategy that targets the exact bottlenecks consuming your CPU and I/O cycles.

Key Takeaway: The slow query log is your evidence-based roadmap; always prioritize adding indexes to queries where the Rows_examined count is disproportionately higher than the result set size.