Master MySQL: Essential Database Design and Performance Guidelines

Database Design

Unified UTF-8 charset for databases and tables to ensure internationalization, compatibility, and avoid conversion errors; use utf8mb4 when storing emojis.

Prefer InnoDB storage engine for transaction support, row‑level locking, better recovery and concurrency; only use MyISAM for special cases.

Add comments to all tables and columns to aid maintenance and data discovery.

Avoid placeholder fields because they are ambiguous, make type decisions difficult, and can cause locking when altered.

Do not store large binary data (images, files) in the database ; store them on external storage services and keep only the file URL in the DB.

Control single‑table size (suggest < 5 million rows) to simplify backup, restore and schema changes; archive historic data or use sharding.

Use MySQL partition tables with caution ; they can cause full‑table locks, performance bottlenecks, and complex maintenance.

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

Never perform stress testing on production databases .

Do not connect development or test environments directly to production .

Database Naming Conventions

Use lowercase letters with underscores, avoid MySQL reserved keywords, keep names under 32 characters, and make them self‑descriptive.

Ensure columns that store the same type of data have identical definitions to avoid implicit conversions that break indexes.

Prefix temporary tables with tmp and backup tables with bak followed by a date.

Field Design

Choose the smallest suitable data type; unsigned integers provide double the range of signed.

Avoid oversized VARCHAR; remember that the length is in characters, not bytes, especially with UTF‑8.

Prefer numeric types for IP addresses and other structured data.

Avoid TEXT and BLOB columns; if necessary, move them to separate tables and never use SELECT * when they are not needed.

Do not use ENUM (low ORDER BY performance) and avoid numeric enum values.

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

Use TIMESTAMP (4 bytes) for dates within 1970‑01‑01 to 2038‑01‑19; otherwise use DATETIME.

Store monetary values with DECIMAL for exact precision; FLOAT / DOUBLE are for non‑critical calculations.

Index Design Guidelines

Limit indexes per table to no more than five; excessive indexes increase write overhead and optimizer planning time.

Never create an index on every column; prioritize columns used in WHERE, GROUP BY, ORDER BY, and joins.

Every InnoDB table must have a primary key; prefer auto‑increment integer IDs and avoid frequently updated or non‑sequential columns (e.g., UUID, MD5).

Avoid creating indexes that are unlikely to be used, such as on low‑cardinality columns or very small tables.

Follow the left‑most principle for composite indexes and place the most selective or frequently used columns first.

Do not use leading wildcards ( LIKE %xx) or functions on indexed columns, as they invalidate the index.

Prefer covering indexes to eliminate the need for a secondary lookup.

Remove redundant or duplicate indexes (e.g., PRIMARY KEY(id), INDEX(id), UNIQUE INDEX(id)) and avoid overlapping indexes.

Foreign keys are discouraged for performance; if used, ensure the referenced columns are indexed.

SQL Development Practices

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

Avoid implicit type conversion in queries; write conditions that match column types exactly.

Design schemas with future extensions in mind.

Reuse existing indexes; avoid double‑percentage patterns in LIKE clauses.

Understand that a query can use only one range column of a composite index.

Limit the number of tables in a join (recommended ≤5) to prevent excessive memory usage.

Batch operations to reduce round‑trips; split large SQL statements into smaller ones for parallel execution.

Replace multiple OR conditions with IN (keep the list under 500 values).

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

Avoid functions or calculations on indexed columns in the WHERE clause; rewrite conditions to use raw column values.

Use UNION ALL when duplicate rows are not a concern to avoid unnecessary sorting.

Operational Guidelines

Batch large write operations (>1 million rows) to avoid master‑slave lag and excessive binary log generation.

Use pt-online-schema-change for altering large tables to minimize locking.

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

Apply the principle of least privilege: restrict accounts to a single database, avoid DROP permissions, and do not allow cross‑database queries.

Following these standards helps ensure that MySQL databases remain performant, maintainable, and reliable throughout the software lifecycle.