The One Index Every Junior DBA Adds That Wastes Disk Space
Every unnecessary index costs disk space and slows writes. Knowing that InnoDB's clustered index covers leftmost-prefix queries eliminates redundant indexes that most novices add by reflex — a small rule that compounds across large tables.
A real blog database design — users, articles, comments, likes, avatars, and tags — is split into six tables by treating entities and relationships as separate modeling units. The user table deliberately holds only three core fields, pushing low-frequency data like avatars into their own table to keep the most-queried table small and cache-friendly. Comments use a self-referencing foreign key on `parentId` to model unlimited nesting, with different cascade strategies for article deletion versus parent-comment deletion.
The central lesson lands on the likes table. A composite primary key of `(userId, postId)` enforces the one-like-per-user constraint without application code and, because InnoDB stores rows in a clustered index ordered by the primary key, queries on `userId` alone hit the leftmost prefix of that index. Adding a separate `KEY userId` duplicates what the clustered index already provides, burning disk space and write overhead for no query benefit. The only extra index needed is on `postId`, which is not the leftmost column.
The same composite-key logic repeats in the many-to-many junction table for article tags. The whole design flows from two questions: is this table an entity or a relationship, and is the field already the leftmost column of an existing composite index.
The instinct to index every foreign key column individually is so common that it qualifies as a reliable signal of DBA inexperience — and it's reinforced by ORMs and migration tools that auto-index foreign keys without checking whether a composite index already covers them.
Keeping the user table to three columns is a deliberate performance trade-off that contradicts the typical impulse to centralize all user profile data; it treats query frequency as the primary factor in column placement, not conceptual grouping.
The `parentId` self-reference for comments is space-efficient but shifts complexity to the query layer — fetching a full comment tree requires recursive CTEs or application-level assembly, a cost the article acknowledges but does not solve.