MySQL Indexes and Histograms to Speed Up Your Queries
summary
Shows how to use MySQL indexes, clustered primary keys, and histograms to give the optimizer real numbers instead of bad guesses — e.g., adding a multi-column index drops the explain cost from 1.5 to 0.7 (≈46% faster) and to 0.66 with a covering index, explain analyze (added in 8.0.18) returns actual row counts and timings, histograms (up to 1024 buckets) are created with ANALYZE TABLE ... UPDATE HISTOGRAM and fix skewed cardinality, and invisible indexes let DBAs safely test removing rarely used indexes.
AI-written summary · about
chapters (13)
- 0:00Introduction and agenda
- 1:47Why query speed matters
- 3:55Data design and normalization
- 6:21Understanding the optimizer
- 10:16What are indexes
- 13:45Multi-column and functional indexes
- 16:32Primary keys and invisible indexes
- 19:18Covering indexes in action
- 21:42Unused indexes and removal
- 23:49Histograms explained
- 28:05Creating and updating histograms
- 30:55Other performance tweaks
- 32:20Resources and next steps
related talks
30mMySQL Indexes and Histograms to Speed Up Your Queries
01h 30mMySQL Indexes, Histograms, Locking Options, and Other Ways to Speed Up Your Queries
50mDiagnosing Database Issues With MySQL 8.0’s Performance Schema
01mHow well do you know your MySQL Indexes?
42mMariaDB and MySQL — What Statistics the Optimizer Uses
46mSupercharge Your SQL with Indexes: a Practical Introduction
45mHistograms in MySQL and MariaDB
21mWriting High Performance SQL Statements