Essential MySQL Naming, Design, and Indexing Standards for Reliable Databases

Database Naming Conventions

All database object names must be lowercase and use underscores.

Avoid MySQL reserved keywords; if used, wrap the name in single quotes.

Names should be meaningful and no longer than 32 characters.

Temporary tables must start with tmp_ and end with a date; backup tables must start with bak_ and end with a date or timestamp.

Columns storing the same data must have identical names and types to prevent implicit conversion and index loss.

Basic Database Design Standards

All tables should use the InnoDB storage engine (default since MySQL 5.6) for transaction support, row‑level locking, and better recovery.

Use utf8mb4 as the unified character set to avoid encoding issues.

Add comments to every table and column to maintain a data dictionary.

Keep single‑table row counts under 5 million; archive logs, shard, or partition large tables.

Use InnoDB partitioning cautiously; prefer physical sharding for massive data.

Separate hot and cold data to keep tables narrow; MySQL limits 4096 columns and 65 535 bytes per row.

Avoid placeholder columns; they hinder type selection and cause locking during changes.

Do not store large binary files (images, documents) in the database; store them on a file server and keep only the path.

Never run performance stress tests on production databases.

Never connect production databases directly from development or test environments.

Field Design Standards

Choose the smallest data type that satisfies storage needs.

Store IP addresses as integers using INET_ATON() and retrieve with INET_NTOA().

Prefer unsigned integers for non‑negative values (e.g., auto‑increment IDs, IPs) because they double the positive range.

VARCHAR(N) counts characters, not bytes; with UTF‑8, VARCHAR(255) can occupy up to 765 bytes.

Avoid TEXT and BLOB types; if necessary, move them to separate extension tables and avoid SELECT *.

Do not use ENUM; altering values requires ALTER and it hinders index usage.

Define columns as NOT NULL whenever possible to save index space and simplify comparisons.

Store timestamps with TIMESTAMP (4 bytes) when the range 1970‑01‑01 to 2038‑01‑19 is sufficient; otherwise use DATETIME (8 bytes).

Financial amounts must use DECIMAL for precise arithmetic; avoid FLOAT / DOUBLE for money.

Index Design Standards

Limit each table to no more than five indexes; excessive indexes increase optimizer planning time and can degrade performance.

Do not create a separate index for every column; prefer composite indexes.

Every InnoDB table must have a primary key; use an auto‑increment integer, avoid frequently updated columns, UUIDs, or large strings.

Common Index Column Recommendations

Columns used in WHERE clauses.

Columns appearing in ORDER BY, GROUP BY, or DISTINCT.

Join keys for multi‑table queries.

Choosing Index Column Order

Place the most selective column (highest distinct‑value ratio) on the leftmost side of a composite index.

Prefer shorter columns on the left to maximize rows per index page.

Put the most frequently queried column first.

Avoid Redundant and Duplicate Indexes

Redundant example: PRIMARY KEY(id), INDEX(id), UNIQUE INDEX(id).

Duplicate example: INDEX(a,b,c), INDEX(a,b), INDEX(a).

Prefer Index Coverage

Use covering indexes that contain all columns needed for a query (WHERE, SELECT, ORDER BY, GROUP BY) to eliminate the need for a secondary lookup on the primary key, turning random I/O into sequential I/O and greatly improving performance.

Index SET Standards (Foreign Keys)

Avoid foreign‑key constraints; instead, create indexes on the related columns.

Implement referential integrity at the application layer when possible.

Foreign keys can slow write operations.

SQL Development Standards

Use prepared statements to reuse execution plans and prevent SQL injection.

Avoid implicit type conversion; it can invalidate indexes (e.g., WHERE id = '111').

Leverage existing indexes; avoid patterns like LIKE '%123%' which prevent index usage.

Only one column of a composite index can be used for a range query; place range columns to the right.

Prefer LEFT JOIN or NOT EXISTS over NOT IN for better index utilization.

Limit the number of joined tables (MySQL allows up to 61, but keep it ≤5).

Batch large write operations (UPDATE/DELETE/INSERT) to avoid massive binlog generation and long‑running transactions.

For large tables, use pt-online-schema-change to alter schema without locking.

Never grant SUPER privilege to application accounts; follow the principle of least privilege.

Use IN instead of multiple OR conditions on the same column (limit to ~500 values).

Avoid ORDER BY RAND(); generate random values in the application instead.

Do not apply functions to indexed columns in WHERE clauses; rewrite date ranges as

WHERE create_time >= '2019‑01‑01' AND create_time < '2019‑01‑02'

.

Prefer UNION ALL over UNION when duplicate rows are impossible.

Break complex large SQL statements into smaller ones to enable parallel execution.

Database Operation Behavior Standards

Batch operations exceeding one million rows; otherwise risk master‑slave lag and huge binlog size.

Use pt-online-schema-change for schema changes on big tables to avoid locking.

Do not grant SUPER to program accounts; reserve it for DBA use.

Apply the least‑privilege principle: program accounts should access only one database and avoid DROP permissions.