In a production database servicing millions of requests, even a few unoptimized queries can monopolize CPU, saturate disk I/O, and cause cascading lock contention.
A systematic query tuning lifecycle consists of three phases:
1
Detection: Capturing slow queries using the Slow Query Log.
2
Analysis: Diagnosing the root cause using EXPLAIN and status counters.
3
Remediation: Eliminating full table scans, filesorts, and temporary tables.
Step 1: Enabling and Configuring the Slow Query Log
1-- Enable the slow query log dynamically
2SETGLOBAL slow_query_log ='ON';
3
4-- Set execution time threshold in seconds (e.g., 1.0 second or 0.5s for 500ms)
5SETGLOBAL long_query_time =0.5;
6
7-- Log queries that do not use any indexes regardless of duration
8SETGLOBAL log_queries_not_using_indexes ='ON';
9
10-- Check log file destination
11SHOW VARIABLES LIKE'slow_query_log_file';
Step 2: Summarizing Slow Queries with mysqldumpslow
Production slow logs grow into gigabytes. Use the mysqldumpslow command-line utility to aggregate and sort query patterns by execution frequency and duration:
1# Top 10 slowest queries sorted by average execution time
2mysqldumpslow -s at -t10 /var/lib/mysql/mysql-slow.log
3
4# Top 10 queries sorted by total count of occurrences
5mysqldumpslow -s c -t10 /var/lib/mysql/mysql-slow.log
Step 3: Eliminating "Using filesort"
When a query contains ORDER BY, MySQL attempts to retrieve rows already sorted from an index. If no suitable index exists, it falls back to filesort in memory (governed by sort_buffer_size).
How to Fix Filesort:
Align the composite index columns with both the WHERE filter and the ORDER BY clause!
1-- Problem Query (Shows "Using filesort"):
2SELECT id, customer_id, order_date, total_amount
3FROM orders
4WHERE customer_id =101
5ORDERBY order_date DESC;
6
7-- Solution: Create composite index covering filter AND sort order!
8CREATEINDEX idx_cust_orderdate ON orders (customer_id, order_date DESC);
With this index, MySQL navigates directly to customer_id = 101 and reads rows in pre-sorted order_date order. Filesort is completely eliminated!
Step 4: Eliminating "Using temporary" on GROUP BY
1-- Problem: Grouping and sorting on different non-indexed columns causes temporary tables
2SELECT country,COUNT(*)
3FROM users
4GROUPBY country;
5
6-- Solution: Add index on group column
7CREATEINDEX idx_user_country ON users (country);
Interactive Quiz• Knowledge Check
Multiple Choice Questions
Question 1
Which MySQL system variable sets the execution time threshold for logging slow queries?
Question 2
Which CLI tool aggregates and summarizes MySQL slow query log files by frequency and average duration?
Question 3
How do you eliminate "Using filesort" for a query filtering WHERE status = 'A' ORDER BY created_at DESC?
Question 4
What does setting log_queries_not_using_indexes = 'ON' accomplish?
Question 5
What buffer in MySQL is utilized to perform in-memory sorting when an index cannot satisfy ORDER BY?
Next Lesson
SQL Query Anti-Patterns & Best Practices
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.