Essential MySQL Design Rules: Engine Choice, Naming, Indexing, and Performance Tips

Rule 1 : Generally use the MyISAM engine, but switch to InnoDB when transaction support is required. Note that MyISAM B‑tree indexes cannot exceed 1000 bytes in total length, and MyISAM stores data and indexes separately, while InnoDB stores data ordered by the clustered primary key, offering superior primary‑key lookup performance.

Rule 2 : Naming conventions.

Database and table names should match the business module they serve.

Tables belonging to the same sub‑module should share a common prefix or suffix.

Table names should reflect the type of data stored.

Column names should correspond to the actual data.

Composite index names should include all indexed column names or abbreviations, follow the column order in the index, and preferably have an idx prefix or suffix.

Constraints and other objects should include the related table or object name.

Rule 3 : Column type definitions.

For CPU‑intensive fields (e.g., calculations, sorting), choose faster types: use TIMESTAMP (4 bytes, min 1970‑01‑01) instead of DATETIME (8 bytes), and prefer integer over floating‑point or character types.

Use VARCHAR for variable‑length strings; avoid CHAR.

Do not store large binary media, log streams, or huge text blobs directly in table columns.

Rule 4 : Tables required by business logic must contain initial values to prevent negative or infinite results.

Rule 5 : Strict normalization is not mandatory; moderate redundancy can reduce joins.

Rule 6 : Split rarely accessed large columns into separate tables to avoid unnecessary I/O.

Rule 7 : Consider horizontal partitioning for large tables, e.g., by time range or modulo of the primary key.

Rule 8 : Create indexes only for columns used in WHERE clauses; avoid extra columns in composite indexes, especially those never used in conditions.

Rule 9 : Define primary keys or unique indexes for columns that uniquely identify a record; use regular indexes for non‑unique columns to improve query speed.

Rule 10 : Even tables with very few rows should have appropriate indexes or primary keys for constraint enforcement.

Rule 11 : Columns that are unique and frequently queried should have a unique index (primary key by default) and be placed first in the WHERE clause; avoid additional composite indexes on them.

Rule 12 : Frequently queried non‑unique columns should also have regular indexes, positioned first in the WHERE clause.

Rule 13 : For non‑unique indexes, ensure the record density does not exceed 0.2; otherwise, the index is not worthwhile.

Rule 14 : When a composite index (or composite primary key) is needed, keep the column order consistent with the order of conditions in SQL statements.

Rule 15 : If multiple columns together form a unique combination, create a unique composite index. Example usage: (a1 op val1, a2 op val2, ... am op valm) m<=n.

Rule 16 : Principles for building composite indexes (assuming columns a, b, c):

Order columns from most selective to least selective.

Match the index column order with the order of conditions in typical queries.

Queries like WHERE a=1, WHERE a>=12 AND a<15, WHERE a=1 AND b<5, WHERE a=1 AND b=7 AND c>=40 can use the index, while WHERE b=10 or WHERE c=221 cannot.

If all needed columns are covered by the index, MySQL can perform a key‑only scan, avoiding full table reads.

When the index column order matches ORDER BY or GROUP BY, the index can be used efficiently.

In some cases, a full table scan followed by sorting is faster, e.g., when the table is already organized by the index or when a large proportion of rows are selected.

Rule 17 : For critical business tables that cannot be accessed via an index, limit sequential scans to no more than 10 rows.

Rule 18 : Write efficient queries.

Batch INSERTs of about 1000 rows achieve best performance; split larger batches and keep the statement size below MySQL's max_allowed_packet.

Avoid using IN on large sets; prefer AND, OR in order of performance.

Drive large result sets with small intermediate result sets (nested loop join principle) to reduce I/O and CPU usage.

Optimize inner loops of nested joins.

Select only required columns; avoid SELECT *.

Use the most selective filter columns in WHERE clauses.

Avoid complex joins and subqueries; high concurrency can degrade MySQL performance due to table‑level locks in MyISAM versus row‑level locks in InnoDB.

Rule 19 : Application‑level optimizations.

Cache relatively static hot data in the application layer to achieve order‑of‑magnitude performance gains.

Merge identical queries to reduce I/O.