Master MySQL: Essential Naming, Design, and Indexing Standards

Database Object Naming Conventions

All object names must be lowercase and use underscores.

Avoid MySQL reserved keywords; if unavoidable, quote them with single quotes.

Names should be meaningful and no longer than 32 characters.

Temporary tables use the tmp_ prefix with a date suffix; backup tables use bak_ with a date or timestamp suffix.

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

Basic Database Design Guidelines

All tables should use the InnoDB storage engine for transaction support, row‑level locking, and better concurrency.

Uniformly use UTF‑8 character set for tables and columns to avoid conversion overhead and index invalidation.

Add comments to tables and columns using the COMMENT clause for automatic data‑dictionary maintenance.

Keep single‑table row counts under 5 million to simplify schema changes, backups, and restores; use archiving, sharding, or partitioning for larger data sets.

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

Separate hot and cold data to keep table width small; InnoDB limits columns to 4096 and row size to 65535 bytes.

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

Store large binary objects (images, files) outside the database and keep only file paths.

Avoid running stress tests on production databases.

Never connect development or test environments directly to production databases.

Field Design Standards

Choose the smallest appropriate data type; larger types increase index size and I/O.

Convert IP strings to integers with INET_ATON and back with INET_NTOA to save space.

Prefer unsigned integers for non‑negative values to double the positive range.

VARCHAR(N) counts characters, not bytes; UTF‑8 stores up to 765 bytes for 255 Chinese characters.

Avoid TEXT/BLOB unless necessary; if used, place them in separate extension tables and never rely on them for indexed queries.

Do not use ENUM; altering values requires costly ALTER statements and ORDER BY on ENUM is inefficient.

Define columns as NOT NULL to avoid extra index space and special NULL handling.

Store timestamps with TIMESTAMP (4 bytes) when the range 1970‑2038 suffices; otherwise use DATETIME (8 bytes).

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

Index Design Guidelines

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

Avoid indexing every column; use composite indexes wisely, placing the most selective columns on the left.

Every InnoDB table must have a primary key; avoid using UUID, MD5, or long strings as primary keys.

Common index candidates: columns used in WHERE clauses, ORDER BY/GROUP BY/DISTINCT, and join keys.

When ordering columns, place the highest‑cardinality column first, then the shortest column, then the most frequently used column.

Eliminate redundant and duplicate indexes (e.g., PRIMARY KEY(id), INDEX(id), UNIQUE INDEX(id)).

Prefer covering indexes that contain all columns needed by a query to avoid secondary‑index lookups.

Avoid foreign key constraints in production; enforce referential integrity at the application layer to reduce write overhead.

SQL Development Best Practices

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

Avoid implicit type conversions; they can invalidate indexes (e.g., comparing an integer column to a string literal).

Leverage existing indexes; avoid leading wildcards (e.g., LIKE '%123%') which prevent index use.

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

Replace NOT IN with LEFT JOIN or NOT EXISTS for better performance.

Limit the number of tables in a join (MySQL allows up to 61, but keeping it under five is advisable).

Batch large write operations (INSERT/UPDATE/DELETE) to avoid massive binlog generation and lock contention.

For schema changes on large tables, use pt-online-schema-change to perform an online, non‑blocking migration.

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

Use explicit column lists in SELECT and INSERT statements; avoid SELECT * and INSERT INTO VALUES without column names.

Replace OR with IN when the list is under 500 items to improve index utilization.

Avoid ORDER BY RAND(); generate random values in the application and query by them instead.

Do not apply functions or calculations to indexed columns in WHERE clauses; rewrite conditions to preserve index usage.

Prefer UNION ALL over UNION when duplicate rows are impossible, as it skips the costly deduplication step.

Break complex, large SQL statements into smaller ones to enable parallel execution and reduce CPU bottlenecks.