Essential MySQL & Oracle Development Best Practices for Robust Databases

MySQL Development Recommendations

Table Design

Prefer the InnoDB storage engine.

Use UNSIGNED for non‑negative numeric values.

Store IPv4 addresses with INT UNSIGNED.

Replace ENUM with TINYINT wherever possible.

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

Choose appropriate temporal types: YEAR for years, DATE for dates, and TIMESTAMP for time‑to‑second precision.

Separate large or rarely accessed columns into auxiliary tables to isolate hot and cold data.

Never store plaintext passwords.

Every table must have a primary key; an UNSIGNED AUTO_INCREMENT column is recommended.

Set the default character set to utf8; use utf8mb4 when emoji or other 4‑byte characters are needed.

Adopt suitable sharding strategies, e.g., “thousand databases, ten tables” or “ten databases, hundred tables”.

a) UTF‑8 stores Chinese characters in 3 bytes and English characters in 1 byte. b) UTF‑8 is uniform and avoids encoding‑related garble. c) For emoji or other 4‑byte symbols, switch to utf8mb4 .

Index Guidelines

Avoid redundant and duplicate indexes.

Do not create indexes on low‑cardinality columns such as gender.

Use covering indexes wisely to reduce I/O and avoid sorting.

SQL Best Practices

Always commit transactions promptly to prevent long‑running locks.

Prefer primary‑key conditions in UPDATE statements.

Replace OR with IN (but keep the IN list reasonably sized).

Use UNION ALL instead of UNION when duplicate elimination is unnecessary.

Avoid ORDER BY RAND() for random ordering.

Implement efficient pagination techniques.

Select only required columns; avoid SELECT *.

Steer clear of nondeterministic functions like NOW(), RAND(), SYSDATE(), CURRENT_USER() in critical queries.

Batch operations to reduce round‑trips to the database.

Common statements to reduce interactions: INSERT ... ON DUPLICATE KEY UPDATE REPLACE INTO INSERT IGNORE INSERT INTO VALUES()

Break complex SQL into smaller statements to avoid large transactions.

Combine multiple ALTER TABLE operations on the same table into a single statement.

Oracle Development Recommendations

Index Usage Tips

Avoid using NOT on indexed columns.

Do not apply functions or calculations to indexed columns (e.g., WHERE SAL * 12 > 25000 is inefficient; rewrite as WHERE SAL > 25000/12).

Do not use IS NULL or IS NOT NULL on indexed columns; instead compare against a constant (e.g., WHERE DEPT_CODE >= 0).

Do not change the data type of indexed columns.

SQL Optimization Techniques

Replace DISTINCT with EXISTS when appropriate.

Inefficient: SELECT DISTINCT DEPT_NO, DEPT_NAME FROM DEPT D, EMP E WHERE D.DEPT_NO = E.DEPT_NO AND E.SEX = 'MAN' Efficient: SELECT DEPT_NO, DEPT_NAME FROM DEPT D WHERE EXISTS (SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO AND E.SEX = 'MAN')

Replace OR with UNION ALL on indexed columns.

Efficient: SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 UNION ALL SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE REGION = 'MELBOURNE' Inefficient: SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = 'MELBOURNE'

Prefer UNION ALL over UNION whenever duplicate removal is not required.

Place ORDER BY on indexed columns, ideally the primary key.

Avoid resource‑intensive constructs such as DISTINCT, UNION, MINUS, INTERSECT that trigger costly sorting.

Use WHERE instead of HAVING when possible.

Eliminate subqueries when a join can achieve the same result, as subqueries often incur high overhead.

Choose the optimal join order and driver table in the WHERE clause.

Avoid SELECT * in Oracle; the engine expands it to all column names via the data dictionary, adding extra processing time.