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

Database Command Naming Conventions

Use lowercase letters and underscores for all object names.

Avoid MySQL reserved keywords; if unavoidable, quote them with 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 type conversion that can invalidate indexes.

Basic Database Design Conventions

All tables should use the InnoDB storage engine (supports transactions, row‑level locking, and better concurrency).

Standardize the character set to UTF8 to avoid conversion issues and index loss.

Add comments to every table and column using the COMMENT clause for data‑dictionary maintenance.

Keep single‑table row counts under 5 million; use archiving, sharding, or partitioning for larger datasets.

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

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

Do not create placeholder columns; they hinder clarity and can cause locking.

Never store large binary files (images, documents) directly in the database; store only file paths.

Avoid running load tests against production databases.

Never connect to production databases from development or test environments.

Database Field Design Conventions

Choose the smallest suitable data type; smaller columns reduce index size and I/O.

Example: Convert IP strings to integers using MySQL functions:

SELECT INET_ATON('192.168.1.1');   -- returns unsigned int
SELECT INET_NTOA(3232235777);    -- returns IP string

Prefer unsigned integers for non‑negative values (e.g., auto‑increment IDs, IP addresses).

Signed INT range: -2147483648 ~ 2147483647; Unsigned INT range: 0 ~ 4294967295.

VARCHAR(N) counts characters, not bytes; UTF8 stores up to 255 Chinese characters (765 bytes). Oversized lengths waste memory.

Avoid TEXT and BLOB types when possible; if needed, place them in separate extension tables and avoid SELECT *.

TEXT/BLOB can only use prefix indexes and have no default values.

Avoid ENUM; changing values requires ALTER and ORDER BY on ENUM is inefficient.

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

Store timestamps with TIMESTAMP (4 bytes) or DATETIME (8 bytes). TIMESTAMP range: 1970‑01‑01 00:00:01 to 2038‑01‑19 03:14:07.

Financial amounts must use DECIMAL for exact precision; FLOAT/DOUBLE are imprecise.

Index Design Conventions

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

Do not create separate indexes on every column; use composite indexes where appropriate.

Every InnoDB table must have a primary key; avoid using frequently updated columns, UUIDs, or long strings as primary keys. Prefer auto‑increment IDs.

Common Index Column Recommendations

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

Columns appearing in ORDER BY, GROUP BY, or DISTINCT. Combine related columns into a single composite index.

Join keys for multi‑table queries.

Choosing Index Column Order

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

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

Put the most frequently used columns first to reduce the number of indexes needed.

Avoid Redundant and Duplicate Indexes

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

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

Prefer Covering Indexes

A covering index contains all columns required by a query (SELECT, WHERE, ORDER BY, GROUP BY), eliminating the need for a second lookup on the primary key and turning random I/O into sequential I/O.

Index SET Guidelines

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

Foreign keys can degrade write performance.

SQL Development Conventions

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 leading wildcards ( LIKE '%123%') which prevent index use.

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 to keep indexes usable.

Separate database accounts per environment; follow the principle of least privilege and avoid granting SUPER to application users.

Never use SELECT *; explicitly list columns to enable covering indexes and reduce I/O.

Always specify column lists in INSERT statements.

Replace subqueries with joins when possible; subqueries often bypass indexes and create temporary tables.

Limit the number of joined tables (MySQL allows up to 61); excessive joins increase memory usage and can cause out‑of‑memory errors.

Batch large write operations (INSERT/UPDATE/DELETE) to avoid long‑running transactions and replication lag.

Use tools like pt-online-schema-change for online schema modifications on large tables.

Replace OR conditions on the same column with IN (max ~500 values) to improve index utilization.

Avoid ORDER BY RAND(); generate a random value in the application and query by that value instead.

Do not apply functions or calculations to indexed columns in the WHERE clause; rewrite as range conditions.

-- Bad: WHERE DATE(create_time) = '20190101'
-- Good: WHERE create_time >= '2019-01-01' AND create_time < '2019-01-02'

Use UNION ALL instead of UNION when duplicate rows are impossible.

Split complex large SQL statements into smaller ones to enable parallel execution and reduce CPU contention.

Database Operation Behavior Guidelines

For batch writes exceeding one million rows, split into multiple transactions to avoid replication lag and massive binlog generation.

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

Never grant SUPER to application accounts; reserve it for DBA use only.

Apply the principle of least privilege: limit each application account to a single database, avoid DROP privileges, and prevent cross‑database access.