Cookbook for Creating INDEXes -- All about Indexing
summary
Step-by-step recipe for building useful indexes: pick all columns that are column=constant first, then allow one range column (equal then range is optimal), then append GROUP BY columns and finally ORDER BY/ LIMIT only if earlier steps are satisfied; InnoDB uses a clustered primary key so prefer a compact PK (natural key or small INT auto_increment), avoid functions/leading-wildcard LIKE, low-cardinality flags, ORs and IN-subqueries (use joins or EXISTS), index-merge is slower than a composite index, and the optimizer often skips a secondary index once selectivity exceeds roughly 20% of rows.
AI-written summary · about
chapters (19)
- 0:00Intro
- 0:16Definitions
- 13:59Algorithm
- 21:52Negatives
- 28:43File Sort
- 31:51Primary Keys
- 47:13Questions
- 48:09Grouping
- 48:51Group Buy
- 49:40Unique Key
- 50:21Index Merge
- 51:49Composite Index
- 52:08Table Reality
- 53:13Use it
- 53:51Black magic
- 54:21Temporary tables
- 54:56Optimization
- 55:35Optimize Table
- 56:33Online Schema Change
related talks
36mFor performance, go to page 5: Indexes
50mHow to choose the right index
47mMySQL Indexing Best Practices
21mWriting High Performance SQL Statements
34mMySQL and InnoDB Performance for the Rest of Us
46mSupercharge Your SQL with Indexes: a Practical Introduction
48mMySQL Indexing - Do It Right or Die
01h 05mWorking with Indexes — Best Practices for MySQL 5.6