Let's Talk About MySQL Query Tuning
summary
Sets long_query_time and watches the slow_queries counter, aiming for aim for ~1 slow query/sec; captures logs to file/table or TCP dump and groups fingerprinted SQL with pt-query-digest or PMM. Uses EXPLAIN (extended/partition), optimizer_trace and EXPLAIN ANALYZE to compare estimates vs actuals. Fixes include adding covering or function-based indexes, turning IN/subqueries into JOINs, including partition keys, and removing SELECT *; tunes sort_buffer, tmp_table_size, join_buffer and buffer_pool; checks persistent stats via sample_pages and auto_recalc.
AI-written summary · about
chapters (22)
- 0:00Introduction
- 4:01User Perception
- 5:11Total Query Time
- 5:21Log Time
- 6:23Optimizer
- 7:24Background Tasks
- 8:42Recap
- 9:15Query Tuning Cycle
- 10:16Slow Query Threshold
- 12:26Slow Query Log
- 14:50Analyze Slow Queries
- 19:40Explain Output
- 21:47Tree Table Join
- 22:41Rows Column
- 24:35Access Type
- 29:17Extra Column
- 36:24Execution Plan Improvements
- 39:14Query Tuning Alternatives
- 46:31Additional Tuning
- 49:00Server Configuration Tuning
- 51:57Engine Independent Statistics
- 52:19MySQL Percona
related talks
41mMySQL Query Optimization Best Practices and Indexing
53mIntroduction to MySQL Performance Tuning - workshop 1hr
31mMySQL Query tuning 101
51mResolving MySQL Problems Quickly
21mWriting High Performance SQL Statements
54mPerformance Analysis and Optimization of MySQL
23mBest Practices for Tuning Slow Postgres Queries
57mPerformance Analysis and Optimization of MySQL