MySQL Database Naming, Design, Indexing, and Development Standards

1. Database Command Naming Standards

All database object names must be lowercase and use underscores, avoid MySQL reserved keywords (quote them if necessary), be meaningful and no longer than 32 characters, use tmp_ prefix for temporary tables and bak_ for backup tables, and keep column names and types consistent across related tables.

2. Basic Database Design Standards

All tables must use the InnoDB storage engine because it supports transactions, row‑level locking, better recovery, and higher concurrency.

Use UTF8 for database and table character sets to avoid conversion errors and index loss.

Add comments to every table and column from the start to build a data dictionary.

Limit single‑table row count to about 5 million to keep schema changes, backups, and restores manageable; use archiving, sharding, or partitioning for larger data.

Use partition tables cautiously ; prefer physical sharding for large datasets.

Separate hot and cold data to keep tables narrow, improve cache hit rates, and reduce I/O.

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

Never perform stress testing on a production database ; only test under controlled conditions.

3. Field Design Standards

Choose the smallest appropriate data type to reduce index size and improve performance.

Avoid TEXT and BLOB types ; if needed, place them in separate extension tables because they cannot use indexes efficiently.

Avoid ENUM types due to costly ALTER operations and poor ORDER BY performance.

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

Store dates/times with TIMESTAMP (4 bytes) or DATETIME (8 bytes) ; use TIMESTAMP when the range fits, otherwise DATETIME.

Use DECIMAL for financial amounts to retain precision, while FLOAT/DOUBLE are for non‑precise values.

4. Index Design Standards

Limit the number of indexes per table to no more than five because excessive indexes degrade write performance and increase optimizer planning time.

Never create an index on every column ; use composite indexes wisely.

Every InnoDB table must have a primary key , preferably an auto‑incrementing integer.

Avoid using frequently updated columns as primary keys and do not use UUID, MD5, or string columns as PKs.

5. Common Index Column Recommendations

Columns appearing in WHERE clauses of SELECT/UPDATE/DELETE.

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

Join columns for multi‑table queries.

6. Choosing Index Column Order

Place the column with the highest cardinality on the leftmost side of a composite index.

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

Put the most frequently used column first.

7. 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) .

8. Prefer Covering Indexes

Covering indexes contain all fields needed by a query, eliminating the need for a second lookup on the primary key and turning random I/O into sequential I/O.

9. Index SET Guidelines

Avoid foreign key constraints; instead, create indexes on the related columns and enforce referential integrity at the application level.

10. SQL Development Standards

Use prepared statements to reduce compilation overhead and prevent SQL injection.

Avoid implicit data‑type conversion; it can invalidate indexes.

Leverage existing indexes; avoid leading % in LIKE patterns.

Use IN instead of multiple OR conditions (limit to ~500 values).

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

Do not apply functions or calculations to columns in WHERE clauses.

Prefer UNION ALL over UNION when duplicate rows are impossible.

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

11. Database Operation Behavior Standards

Batch large write operations (>1 million rows) into smaller chunks to avoid master‑slave lag and massive binlog generation.

Use pt-online-schema-change for schema changes on large tables to avoid locking and long‑running DDL.

Do not grant SUPER privileges to application accounts; follow the principle of least privilege.

Application accounts should be limited to a single database, have no DROP privileges, and avoid cross‑database access.