MySQL Schema Review
summary
Demo creates two identical tables with 4 million rows and compares INT vs BIGINT, finding about 250 MB extra for BIGINT; removing a duplicate index reclaims ~200 MB; switching 36-char UUID primary keys to unsigned AUTO_INCREMENT shrinks two tables by ~50% and drops idx_frac 1.12→0.38. Includes concrete queries against information_schema to list largest tables, shows how to group by schema/table and sort by index fraction, and uses pt-duplicate-key-checker, Percona index_statistics and performance_schema to find unused indexes.
AI-written summary · about
chapters (17)
- 0:00Intro
- 2:35Introduction - Sample Schema
- 2:55Key Considerations
- 5:53Data Types - Extra Considerations
- 8:13Indexing - High Level
- 11:09Indexing - Extra Considerations
- 14:31Profiling your Schema
- 16:18Finding Largest Schema
- 17:16Find Largest Tables in Schema
- 17:54Breaking it down...
- 20:05Finding duplicate keys...
- 21:12Finding Unused Indexes
- 22:32Combine with base index statistics...
- 23:41Drop duplicate keys
- 24:00Changing data types on event tables
- 25:42Schema breakdown - revisited...
- 26:13Common Issues
related talks
46mSupercharge Your SQL with Indexes: a Practical Introduction
31mDesigning Effective Schema for Innodb
34mMySQL and InnoDB Performance for the Rest of Us
59mBoosting MySQL Performance
35mMySQL Indexes and Histograms to Speed Up Your Queries
55mMySQL 8.0: Not Only Good, it's GREAT
30mMySQL Indexes and Histograms to Speed Up Your Queries
42mThings every developer absolutely, positively needs to know about database indexing