Good schema design begins with choosing appropriate data types for each column. Smaller and simpler types consume less storage, fit more rows per page, improve cache hit rates, and compare more efficiently. For identifier columns, numeric types like INT and BIGINT are almost always preferable to VARCHAR because they are fixed-width, faster to compare, and produce smaller, denser indexes. Care should also be taken with NULLable columns in composite indexes, because NULL values can reduce selectivity and complicate cardinality estimates used by the optimizer. Choosing BIGINT for primary keys on busy tables provides a much larger ID space than INT, avoiding emergency migrations when smaller ranges are exhausted; small auto-increment types can run out of values, causing insert failures, while BIGINT gives headroom for very large tables. Auto-increment contention can also appear in high-write workloads when many concurrent inserts fight over the same hot page, and this can be alleviated by sharding or partitioning inserts across multiple tables.
Primary key design has far-reaching consequences. In InnoDB, secondary indexes implicitly include the primary key, so a wide composite primary key inflates every secondary index on the table. A common pattern is to use a narrow surrogate key (such as a BIGINT auto-increment) as the primary key and keep large natural keys as secondary indexed columns. Random keys such as UUIDv4 cause inserts to land at random positions in the B-tree, leading to page splits, fragmentation, and poor cache locality. When UUID-like identifiers are required, ordered schemes such as UUIDv7 or mapping to a sequential integer reduce this hot-spotting. Very large BLOB or TEXT columns similarly bloat rows and indexes; storing only references in main tables and moving the large values to separate tables or external storage keeps hot paths compact and efficient. Timezone handling deserves thought as well: storing timestamps in UTC and converting at the application layer, or via indexed generated columns for common local time views, avoids expensive CONVERT_TZ calls that block index usage on datetime columns.
Schema design also involves tradeoffs between normalization and denormalization. Proper normalization reduces duplication, ensures consistency, and keeps indexes small, which generally helps performance. Denormalization, by contrast, can dramatically speed up read-heavy workloads that would otherwise require expensive joins, at the cost of extra storage and more complex writes that must keep the duplicates in sync. Generated columns—either virtual or stored—offer a middle ground by precomputing expensive expressions or JSON extractions; when stored and indexed, they let queries access derived values as if they were ordinary columns, often without the function-call overhead that would otherwise prevent index use, which is particularly helpful for JSON columns where nested paths are otherwise hard to index. Histograms, created with ANALYZE TABLE ... UPDATE HISTOGRAM ON column, give the optimizer more accurate selectivity estimates for skewed distributions where simple cardinality statistics are misleading. Finally, enabling innodb_file_per_table stores each table in its own tablespace, which avoids shared tablespace bloat and allows per-table operations like shrinking or moving to faster storage, while modern row formats such as DYNAMIC or COMPRESSED keep large off-page values efficient.