10 Essential MySQL Development Rules Every DBA and Developer Should Follow

1. Use InnoDB storage engine for tables

Since MySQL 8.0 the MyISAM engine is deprecated; every table should use InnoDB and include an auto‑increment primary key ID.

2. Choose simple, small data types

Prefer

int

for IPv4 addresses (convert with functions) and store monetary values as integers in cents. Use

datetime

instead of

timestamp

for a larger range and smaller storage (5 bytes).

<code>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 |
+-----------------------+
1 row in set (0.00 sec)</code>

3. Use lowercase names with underscores

Database, table, and column names should be lowercase and separated by underscores; set

lower_case_table_names

to 1 for case‑insensitive storage.

4. Avoid ENUM and large text/blob columns

Replace

ENUM

with

TINYINT

and keep

TEXT

/

BLOB

out of business tables unless absolutely necessary.

5. Use UTF‑8 (or UTF‑8 mb4) character set

UTF‑8 is more universal than GBK or latin1; switch to

utf8mb4

when you need to store emojis.

6. Select only required columns, avoid SELECT *

Fetching specific columns reduces bandwidth and can enable covering indexes.

7. Define columns as NOT NULL by default

Non‑null columns make

COUNT()

results more accurate because NULL values are excluded.

8. Do not index low‑cardinality columns

Columns like

sex

or

status

have low selectivity; generally keep the number of indexes per table under 4‑5.

9. Minimize use of OR and prefer UNION ALL

Let the application handle logical branching instead of the database, and use

UNION ALL

to avoid costly deduplication.

10. Continuously monitor production SQL

Use tools such as Percona Toolkit to capture and analyze live queries.