Writing High Performance SQL Statements
summary
Walkthrough of practical steps for faster MySQL queries: use EXPLAIN type/possible_keys/key/rows to see that adding an index turns a table scan of ~200 rows into a range scan (~313 rows) or a constant lookup (1 row), prefer compact types and NOT NULL, favor a covered index (all needed columns) and correct leftmost ordering for multicolumn keys, avoid wrapping indexed columns in functions because functions disable indices, know query cache limits and use Percona tools (pt-query-digest, pt-index, pt-visual-explain) to find hot queries and unused indexes.
AI-written summary · about
chapters (17)
- 0:00High-performance SQL intro
- 1:06EXPLAIN tool for queries
- 3:16EXPLAIN indexed vs non-indexed
- 5:44Schema optimization mistakes
- 7:56Normalized vs denormalized trade-offs
- 9:02Optimal database indices
- 11:57Multi-column index ordering
- 13:44Hash indices limitations
- 14:28Reduce data retrieval
- 15:35LEFT JOIN and WHERE pitfalls
- 16:20Functions disable index usage
- 17:30Aggregate functions impact
- 17:55Query cache alternatives
- 18:37Right database engine
- 19:20Performance tips
- 19:42Query analysis tools
- 20:28Q&A
related talks
34mMySQL and InnoDB Performance for the Rest of Us
41mMySQL Query Optimization Best Practices and Indexing
50mHow to choose the right index
36mFor performance, go to page 5: Indexes
55mLet's Talk About MySQL Query Tuning
01h 30mMySQL Indexes, Histograms, Locking Options, and Other Ways to Speed Up Your Queries
01mHow well do you know your MySQL Indexes?
25mThe Secret Life of SQL: How to Optimize Database Performance