Essential MySQL Naming Conventions and Performance Tips for Robust Databases

Naming Conventions

1. Use lowercase letters and underscores for database, table, and column names. MySQL's lower_case_table_names setting determines case sensitivity; mixing cases can cause duplicate tables like abc, Abc, ABC.

2. Limit names to 32 characters. Although MySQL supports up to 64 characters, restricting to 32 improves readability and reduces transmission overhead.

3. Use the InnoDB storage engine. InnoDB provides transactions, row‑level locking, better recovery, and superior performance on modern hardware compared with MyISAM.

4. Avoid MySQL reserved words. Using reserved words forces back‑ticks around identifiers, complicating SQL and scripts.

5. Do not use partitioned tables. Partitions impose strict key requirements and make DDL, sharding, and recovery harder.

6. Store non‑negative numbers with UNSIGNED . Unsigned types double the positive range for the same storage size.

7. Store IPv4 addresses as INT UNSIGNED . This uses 4 bytes versus 15 bytes for CHAR(15). Example:

SELECT INET_ATON('209.207.224.40');  -- returns 3520061480
SELECT INET_NTOA(3520061480);      -- returns 209.207.224.40

8. Prefer TINYINT over ENUM . Modifying ENUM values requires costly online DDL.

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

10. INT always occupies 4 bytes; the number in INT(4) only affects display width.

11. Choose TIMESTAMP over DATETIME for second‑precision timestamps. TIMESTAMP uses 4 bytes, supports automatic initialization and update, while DATETIME uses 8 bytes.

12. Define all columns as NOT NULL . NULL values waste space and degrade index efficiency; use sentinel values instead.

MySQL Usage Tips

1. Separate large or infrequently accessed columns into auxiliary tables. This keeps hot data in memory and reduces I/O.

2. Never store plaintext passwords. Store salted, hashed strings that cannot be decrypted.

3. Ensure every table has a primary key, preferably an unsigned auto‑increment column.

4. Avoid redundant or duplicate indexes. Redundant indexes increase maintenance overhead and I/O.

5. Do not index low‑cardinality columns such as gender. The performance gain is negligible while I/O increases.

6. Use covering indexes to eliminate the need for table lookups. Example: SELECT email, uid FROM user_email WHERE uid = xx; Adding INDEX(uid, email) can improve performance when uid is not the primary key.

7. Prefer IN over OR (keep list size < 1000).

8. Use UTF8 charset; switch to UTF8MB4 for emojis.

9. Use UNION ALL instead of UNION to avoid unnecessary sorting.

10. Avoid ORDER BY RAND() due to heavy temporary table creation. Generate random primary keys first, then fetch rows.

11. Implement efficient pagination. Instead of LIMIT 10000,10, use a "last timestamp" condition:

SELECT * FROM table WHERE TIME < last_TIME ORDER BY TIME DESC LIMIT 10;

Or use a sub‑query to fetch IDs and join.

12. Select only required columns; avoid SELECT * . This reduces bandwidth, enables covering indexes, and isolates schema changes.

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

14. Adopt appropriate sharding strategies (e.g., thousand databases with ten tables each).

15. Batch statements to reduce round‑trips. Examples:

INSERT ... ON DUPLICATE KEY UPDATE
REPLACE INTO ...
INSERT IGNORE ...
INSERT INTO ... VALUES (...)

16. Split complex SQL into smaller statements to avoid large transactions and improve query cache usage.

17. Consolidate multiple ALTER TABLE operations into a single statement. Example:

ALTER TABLE t ADD COLUMN b VARCHAR(10), ADD INDEX idx_aa(aa);

18. Avoid stored procedures, triggers, views, and custom functions. They add performance overhead and complicate scaling.

19. Do not grant SUPER privileges to application accounts. Super privileges bypass read‑only restrictions and create hard‑to‑trace issues.

20. Keep business logic out of the database. Place it in the application layer to maintain scalability and separation of concerns.