Volume 4 – MySQL DBA
Printable Notes
Module 3: Query Performance & Optimization
MODULE 3 – QUERY PERFORMANCE & OPTIMIZATION
Objective
This module teaches how to identify, analyze, and fix slow queries using evidence-based DBA
techniques.
1. What is a Slow Query?
A slow query is not defined by time alone.
Correct DBA definition: - A query that takes longer than expected for its workload - OR a query that
runs frequently and consumes resources
Examples: - 30 sec report query at night → OK - 2 sec query running 10,000 times → BAD
2. Slow Query Log (Foundation)
Slow Query Log records queries that exceed a defined threshold.
Key Variables
slow_query_log
slow_query_log_file
long_query_time
long_query_time
• Unit: seconds
• Default: 10 (too high for production)
Recommended values: - Dev: 0.5–1 sec - QA/UAT: 1–2 sec - Production: 2–5 sec
1
3. Slow Query Log Entry – Key Fields
Field Meaning
Query_time Total execution time
Lock_time Time waiting for locks
Rows_examined Rows scanned by MySQL
Rows_sent Rows returned to client
Golden Rule
Rows_examined >> Rows_sent ⇒ Missing / bad index
4. Root Cause Identification from Slow Log
Pattern Meaning
High Query_time, Low Lock_time Index / logic issue
High Lock_time Blocking transaction
High Rows_examined Full table scan
Same query repeated Application design issue
5. EXPLAIN – Understanding Execution Plans
EXPLAIN shows how MySQL plans to execute a query.
EXPLAIN SELECT ...;
Important EXPLAIN columns:
Column Description
type Access method (MOST IMPORTANT)
key Index used
rows Estimated rows scanned
Extra Additional operations
2
6. EXPLAIN type Values (MEMORIZE)
type Meaning Verdict
system Single row Excellent
const PK / unique Excellent
eq_ref PK join Excellent
ref Index lookup Good
range Range scan OK
index Full index scan Risk
ALL Full table scan BAD
Rule:
ALL indicates full table scan and requires investigation.
7. EXPLAIN ANALYZE (MySQL 8+)
EXPLAIN ANALYZE SELECT ...;
Difference: - EXPLAIN → estimates - EXPLAIN ANALYZE → actual execution time and rows
Used to validate tuning improvements.
8. Index Cardinality
Cardinality = number of distinct values in a column.
Cardinality Index usefulness
High Very useful
Low Often ignored
Low-cardinality columns (status, gender) are poor single-column indexes.
9. Composite Index Design
Composite index = index on multiple columns.
3
Ordering Rule (VERY IMPORTANT)
Place most selective (highest cardinality) column first.
Leftmost Prefix Rule
Index (A, B, C) works for: - A - A + B - A + B + C
Does NOT work for: - B alone - C alone
10. Covering Index
If all required columns are in the index:
• MySQL reads index only
• Table access avoided
EXPLAIN shows:
Using index
11. Query Anti-Patterns (COMMON MISTAKES)
Anti-Pattern Effect
Function on indexed column Index not used
LIKE '%abc' Full table scan
OR conditions Index dropped
OFFSET pagination Scales poorly
SELECT * Extra IO
Data type mismatch Index ignored
12. Pagination Best Practice
Avoid:
LIMIT 20 OFFSET 500000
Use keyset pagination:
4
WHERE id > last_id LIMIT 20
13. DBA Performance Tuning Flow
Slow Query Log
→ Identify query
→ EXPLAIN
→ Fix index / logic
→ EXPLAIN ANALYZE
→ Verify improvement
14. Senior DBA Golden Rules
• Evidence before tuning
• Fix queries before buffers
• EXPLAIN never lies
• Cardinality decides index value
• Avoid query anti-patterns
FINAL SUMMARY (MEMORIZE)
• Slow query = context-based
• Slow query log = evidence
• Rows_examined reveals truth
• EXPLAIN shows execution path
• Index design matters more than count
END OF MODULE 3 PRINTABLE NOTES