Essential MySQL Naming Conventions and Performance Tips for Robust Database Design

Naming Conventions

Database, table, and column names must be lowercase and use underscores.

Names should not exceed 32 characters to improve readability and reduce transmission overhead.

Use the InnoDB storage engine for its transactional support, row‑level locking, and better concurrency.

Avoid MySQL reserved words in identifiers to prevent the need for back‑ticks.

Do not use partitioned tables; they complicate DDL and sharding.

Prefer UNSIGNED for non‑negative numeric values to enlarge the usable range.

Store IPv4 addresses as INT UNSIGNED and convert with INET_ATON / INET_NTOA.

Replace ENUM with TINYINT to simplify schema changes.

Use VARBINARY for case‑sensitive variable‑length strings or binary data.

INT always occupies 4 bytes; the number in parentheses only affects display width.

Prefer TIMESTAMP over DATETIME for second‑precision timestamps because it uses 4 bytes and supports automatic initialization and update.

Define every column as NOT NULL to avoid extra null‑bitmap storage and improve index efficiency.

MySQL Usage Tips

Separate large or rarely accessed columns into auxiliary tables to keep hot data in cache.

Never store plaintext passwords; store salted, hashed values instead.

Every table must have a primary key, preferably an UNSIGNED auto‑increment column.

Avoid redundant or duplicate indexes; use prefix indexes when appropriate.

Do not index low‑cardinality columns such as gender.

Use covering indexes to retrieve all needed columns from the index and avoid extra lookups.

Prefer IN over OR for set membership checks (keep the list under 1000 items).

Use UTF‑8 (or UTF‑8MB4 for emojis) as the default character set.

Replace UNION with UNION ALL to skip unnecessary sorting.

Avoid ORDER BY RAND(); instead fetch random primary‑key values first.

Implement efficient pagination: avoid large offsets and use a "last seen" timestamp or key with a limited LIMIT.

Select only required columns instead of SELECT * to reduce bandwidth and enable index‑only scans.

Do not use nondeterministic functions like NOW(), RAND(), SYSDATE() in statements that need replication consistency.

Adopt suitable sharding strategies (e.g., many databases with few tables each) to facilitate horizontal scaling.

Batch DML statements (e.g., INSERT ... ON DUPLICATE KEY UPDATE, REPLACE, INSERT IGNORE) to reduce round‑trips.

Split complex SQL into smaller statements to improve cacheability and reduce transaction size.

Combine multiple ALTER TABLE actions into a single statement to avoid repeated table rebuilds.

Avoid stored procedures, triggers, views, and custom functions because they add performance overhead and complicate scaling.

Never grant SUPER privileges to application accounts; it bypasses read‑only safeguards.

Keep business logic out of the database; use application layers for logic and treat the database as a pure storage engine.