Essential MySQL Standards: Naming, Design, Indexing, and SQL Best Practices

1. Database Command Standards

All database object names must be lowercase and use underscores.

Avoid MySQL reserved keywords; if used, wrap with single quotes.

Names should be meaningful and not exceed 32 characters.

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

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

2. Basic Database Design Standards

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

Use UTF‑8 character set for databases and tables to avoid encoding issues.

Add comments to tables and columns for data dictionary maintenance.

Keep single table size under 5 million rows; archive or shard data when larger.

Use physical sharding instead of MySQL partition tables for large datasets.

Separate hot and cold data to reduce table width and improve cache efficiency.

Do not create placeholder columns; they hinder clarity and type selection.

Avoid storing large binary data (images, files) in the database; store only file paths.

Never perform stress testing on production databases.

Never connect development or test environments directly to production databases.

3. Database Field Design Standards

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

Convert strings to numeric types when possible, e.g., store IP addresses as integers using inet_aton and retrieve with inet_ntoa.

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

VARCHAR length counts characters, not bytes; UTF‑8 stores three bytes per Chinese character.

Avoid TEXT/BLOB types; if necessary, move them to separate extension tables.

Do not use ENUM; it requires ALTER for changes and has poor ORDER BY performance.

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

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

Use DECIMAL for financial amounts to retain precision.

4. Index Design Standards

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

Avoid creating an index on every column; use composite indexes wisely.

Every InnoDB table must have a primary key, preferably an auto‑increment ID; avoid frequent‑update columns, UUIDs, or strings as primary keys.

Prefer covering indexes that include all columns used in SELECT, WHERE, ORDER BY, and GROUP BY.

Do not create redundant indexes (e.g., primary key + index on same column) or overlapping indexes (e.g., index(a,b,c) plus index(a,b)).

When ordering index columns, place the most selective and shortest columns on the left.

5. Common Index Column Recommendations

Columns appearing in WHERE clauses.

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

Join columns for multi‑table queries.

6. Choosing Index Column Order

Place the highest‑cardinality column first.

Place shorter columns first to maximize rows per page.

Place the most frequently used columns first.

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

8. Prefer Covering Indexes

Covering indexes contain all fields needed for a query, eliminating the need for a secondary lookup and converting random I/O to sequential I/O.

9. Index SET Guidelines

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

10. Database SQL Development Standards

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

Avoid implicit type conversions; they can invalidate indexes.

Leverage existing indexes; avoid patterns like LIKE '%value%' which prevent index use.

Do not use SELECT *; specify required columns to enable covering indexes.

Always list columns in INSERT statements.

Replace subqueries with JOINs when possible to avoid temporary tables.

Limit the number of tables joined; excessive joins increase memory usage and can cause server instability.

Batch write operations and split large DML into smaller chunks to reduce lock contention and binlog size.

Use pt-online-schema-change for online schema changes on large tables.

Grant minimal privileges to application accounts; avoid SUPER and cross‑database permissions.