It’s Not Always About the Index: The Hidden Secrets of Query Performance
summary
Builds a small online-store schema (stock and price) on MySQL in Docker, runs live queries with explain analyze and cache flush, and shows a single-item lookup dropping from 62 ms to 8 ms when using a B‑tree index but no speedup — sometimes slowdown — when filtering high‑cardinality or large-result conditions. Covers optimizer decisions, B+ tree vs trigram needs for prefix/substring searches, problems with functions/casts on WHERE columns, and fixes like expression index and partial indexes.
AI-written summary · about
chapters (9)
- 0:00Introduction
- 3:00Online store system for NDC
- 8:12What is an index
- 13:53Creating indexes in MySQL
- 19:33Database optimizer
- 23:38Indexes slowing queries
- 29:02Game: Query breaks index?
- 36:50Advanced index tricks
- 47:19Summary
related talks
46mSupercharge Your SQL with Indexes: a Practical Introduction
36mFor performance, go to page 5: Indexes
25mThe Secret Life of SQL: How to Optimize Database Performance
42mThings every developer absolutely, positively needs to know about database indexing
01h 30mMySQL Indexes, Histograms, Locking Options, and Other Ways to Speed Up Your Queries
01mHow well do you know your MySQL Indexes?
47mMySQL Indexing Best Practices
30mMySQL Indexes and Histograms to Speed Up Your Queries