Designing Effective Schema for Innodb
summary
Practical, actionable rules for InnoDB schema: keep a large buffer pool and use file-per-table; store UUIDs as VARBINARY(36) not CHAR(36) (CHAR(36) with utf8 becomes 108 bytes); prefer AUTO_INCREMENT or clustered keys (for example make user_id,object_id the PK) to avoid random leaf-page inserts—random inserts drop a laptop test from ~20,000 rows/s to <100 rows/s; use INT(4) for IPv4, TINYINT for boolean flags, TIMESTAMP (4 bytes) instead of DATETIME when appropriate, and avoid large TEXT/BLOB in-row to reduce I/O.
AI-written summary · about
chapters (10)
- 0:00InnoDB schema design intro
- 1:04InnoDB internals core
- 9:35File-sharing app schema
- 13:35Schema performance issues
- 15:03Schema optimization tips
- 21:43Optimal data types
- 26:03UUID and random inserts impact
- 27:53Primary key ordering
- 29:22Secondary keys and buffer pool
- 30:30Reorganizing primary keys
