MySQL Query Optimization Best Practices and Indexing
summary
Claims concrete fixes: treat queries over 500 ms as slow, capture and fingerprint them with PMM query analyzer or the slow query log, read EXPLAIN (JSON on 5.7+) and fix plans by updating stats with ANALYZE TABLE, collapse duplicate or redundant indexes and prefer a numeric primary key plus leftmost composite indexes. Use Percona Toolkit (pt-visual-explain, pt-duplicate-key-checker) and consider ProxySQL query rewrites when indexing and query rewrite fail.
AI-written summary · about
chapters (25)
- 0:00Intro
- 1:57About Percona
- 4:22Indexing Basics
- 5:11Why do we need indexes?
- 6:35Traversal
- 7:20Leaf Nodes
- 8:46B-tree Structure
- 9:22Slow Index Lookups
- 12:33The Optimizer
- 13:51MySQL Optimizer
- 14:26Cost Model
- 15:25Finding and Identifying Slow Queries
- 18:38Slow Query Tools
- 20:12PMM/QAN
- 23:55Explain Plan (JSON)
- 25:16Explain Plan (pt-visual-explain)
- 26:11Cost Based Access Method
- 27:12Query Execution
- 27:35Indexing Best Practices
- 33:04Query Optimization Best Practices
- 36:27MySQL Index Types
- 37:31Advanced Indexing
- 38:22If indexes not enough
- 39:32Final Thoughts
- 40:27References and Credits
related talks
55mLet's Talk About MySQL Query Tuning
21mWriting High Performance SQL Statements
51mResolving MySQL Problems Quickly
34mMySQL and InnoDB Performance for the Rest of Us
01h 30mMySQL Indexes, Histograms, Locking Options, and Other Ways to Speed Up Your Queries
56mOptimization Techniques in MySQL
31mMySQL Query tuning 101
01h 02mHow-to MySQL Performance Diagnostic Using Percona PMM