How to choose the right index
summary
Practical rules for indexes: use EXPLAIN and slow query logs to find the hot queries, then pick indexes that cut scanned rows — a billion-row scan that returns ~1,000 rows becomes feasible with a composite index and can drop to ~100 rows with a covering index. Favor composite + covering indexes where equality columns come first, avoid many low-value indexes, and prevent random I/O by designing for sequential scans. Examples reference MySQL optimizer behavior (5.5/5.6), index-merge limits, and Fractal-tree/TokuDB storage to reduce I/O.
AI-written summary · about
chapters (10)
- 0:00Introduction and speaker background
- 1:32Why indexes matter for performance
- 2:35Basic definitions of tables and indexes
- 8:29Selecting important queries to index
- 10:56Three simple rules for good indexes
- 13:32Rule one: Get fewer data rows
- 25:04Rule two: Avoid point queries
- 32:53Rule three: Avoid sorting overhead
- 37:20Fractal tree indexes explained
- 42:04Q&A and final remarks
related talks
21mWriting High Performance SQL Statements
36mFor performance, go to page 5: Indexes
34mMySQL and InnoDB Performance for the Rest of Us
57mCookbook for Creating INDEXes -- All about Indexing
47mMySQL Indexing Best Practices
46mSupercharge Your SQL with Indexes: a Practical Introduction
42mThings every developer absolutely, positively needs to know about database indexing
41mMySQL Query Optimization Best Practices and Indexing