Essential MySQL & Oracle Development Guidelines for Tables, Indexes, and SQL

Table design recommendations (MySQL)

Prefer the InnoDB storage engine.

Use UNSIGNED for non‑negative numeric values.

Store IPv4 addresses as INT UNSIGNED.

Replace ENUM with TINYINT wherever possible.

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

Choose appropriate date/time types: YEAR for years, DATE for dates, TIMESTAMP for second‑precision times; avoid mixing DATETIME and TIMESTAMP.

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, preferably an UNSIGNED auto‑increment column.

Use UTF‑8 charset; switch to UTF‑8MB4 when emoji or other 4‑byte characters are needed.

Adopt suitable sharding strategies (e.g., thousands of databases with ten tables each).

Indexing guidelines (MySQL)

Avoid redundant or duplicate indexes.

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

Use covering indexes to reduce I/O and eliminate sorting when possible.

SQL best practices (MySQL)

Always commit transactions promptly to prevent long‑running locks.

Prefer primary‑key conditions in UPDATE statements.

Replace OR with IN (keep the list size reasonable).

Use UNION ALL instead of UNION to avoid unnecessary sorting.

Avoid ORDER BY RAND().

Implement efficient pagination techniques.

Select only required columns; avoid SELECT *.

Do not use nondeterministic functions like NOW(), RAND(), SYSDATE(), CURRENT_USER() in queries that need stable results.

Reduce round‑trips to the database by using batch statements such as:

INSERT ... ON DUPLICATE KEY UPDATE

REPLACE INTO

INSERT IGNORE

INSERT INTO VALUES()

Split complex queries into smaller ones to keep transactions short.

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

Oracle indexing tips

Avoid using NOT or calculations on indexed columns.

Rewrite expressions to use the indexed column directly (e.g., WHERE SAL > 25000/12 instead of WHERE SAL * 12 > 25000).

Prefer range conditions over IS NULL / IS NOT NULL on indexed columns.

Do not change the data type of indexed columns.

Oracle SQL optimization techniques

Replace DISTINCT with EXISTS when appropriate.

Use UNION ALL instead of OR on indexed columns.

Prefer UNION ALL over UNION whenever duplicate elimination 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.

Minimize subqueries; rewrite them as joins or other set‑based operations.

Choose the optimal join order and driver table.

Avoid selecting * in Oracle; explicit column lists reduce dictionary lookups and improve performance.