10 Essential MySQL Development Rules Every DBA and Developer Should Follow

When DBAs collaborate with developers, clear MySQL development conventions help avoid performance pitfalls and maintain data integrity. The following ten rules summarize widely‑accepted best practices.

Use InnoDB engine for every table and include an auto‑increment primary‑key id. MySQL 8.0 no longer supports MyISAM.

Prefer simple, small data types . Store IPv4 as INT using INET_ATON() / INET_NTOA(), and represent money as an integer (cents). Use DATETIME instead of TIMESTAMP for a larger range and smaller storage (5 bytes).

Name databases, tables, and columns in lowercase with underscores . The lower_case_table_names variable controls case sensitivity (0 = case‑sensitive, 1 = case‑insensitive).

Avoid ENUM types ; replace them with TINYINT enumerations.

Do not place large TEXT or BLOB columns in business tables unless absolutely necessary.

Set the default charset to utf8 , and switch to utf8mb4 when emoji or other 4‑byte characters are required.

Select only required columns and avoid SELECT *. This reduces network bandwidth and enables covering indexes.

Define columns as NOT NULL by default to prevent NULL values from skewing COUNT() results.

Do not create indexes on low‑cardinality columns (e.g., sex, status). Evaluate selectivity with SELECT COUNT(DISTINCT col)/COUNT(*); aim for a ratio close to 1 and keep indexes per table under 4‑5.

Avoid OR clauses in SQL and prefer UNION ALL over UNION to skip unnecessary deduplication and sorting.

Continuously monitor production SQL and capture statements for analysis, using tools such as percona‑toolkit.

Example of converting an IPv4 address to an integer and back:

mysql> SELECT INET_ATON('192.168.56.132');
+-----------------------------+
| INET_ATON('192.168.56.132') |
+-----------------------------+
| 3232249988                  |
+-----------------------------+

mysql> SELECT INET_NTOA(3232249988);
+-----------------------+
| INET_NTOA(3232249988) |
+-----------------------+
| 192.168.56.132        |
+-----------------------+

Adhering to these guidelines encourages smoother collaboration between DBAs and developers, leads to more efficient queries, and ultimately results in a more reliable and performant MySQL environment.