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).

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)

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.