Things every developer absolutely, positively needs to know about database indexing
summary
Shows B trees and why order matters, runs EXPLAIN in MySQL and reads the type column to see access patterns, uses a demo table of 2.25 million rows, and demonstrates range, index and full table scans. Replaces YEAR(created_at) with a BETWEEN to let indexes work, forces an index to reveal MySQL chooses full scans when row fetches cause IO, builds a covered index-only scan to cut 600ms queries down to ~92ms and finally under 1ms.
AI-written summary · about
chapters (22)
- 0:00Introduction
- 10:32Tradeoffs
- 11:07Execution plan
- 12:52Access type
- 13:26Cons
- 14:59Ref and Range
- 16:07Index
- 17:03Recap
- 19:26Indexing
- 20:20Query Execution Plan
- 21:08Possible Keys and Key
- 21:37Functions
- 23:00Functions based indices
- 24:17Removing the function
- 25:23Not using the index
- 26:03Force index
- 29:14Full table scan
- 30:40Index only scan
- 32:18Force push
- 33:51Multicolumn indexing
- 36:15Inequality operators
- 38:27Index vs Query
related talks
47mMySQL Indexing Best Practices
36mFor performance, go to page 5: Indexes
46mSupercharge Your SQL with Indexes: a Practical Introduction
01mHow well do you know your MySQL Indexes?
49mIt’s Not Always About the Index: The Hidden Secrets of Query Performance
21mWriting High Performance SQL Statements
50mHow to choose the right index
25mThe Secret Life of SQL: How to Optimize Database Performance