Essential MySQL Database Design and Indexing Best Practices

1. Database Command Conventions

Use lowercase letters with underscores for all object names.

Avoid MySQL reserved keywords in object names; if necessary, quote them with single quotes.

Names should be meaningful and no longer than 32 characters.

Temporary tables must start with tmp_ and end with a date; backup tables with bak_ and a date or timestamp.

Columns storing the same data must have identical names and types to prevent implicit type conversion and index loss.

2. Basic Database Design Standards

All tables should use the InnoDB storage engine for transaction support, row‑level locking, and better concurrency.

Standardize the character set to UTF8 to avoid conversion issues and index failures.

Add comments to every table and column to maintain a data dictionary.

Keep single‑table row counts under 5 million to simplify schema changes, backups, and restores.

Prefer archiving, sharding, or partitioning for large datasets.

Use partition tables cautiously; prefer physical sharding for very large data.

Separate hot and cold data to reduce table width and improve cache efficiency.

Do not create placeholder columns; they hinder clarity and type selection.

Never store large binary objects (images, files) directly in the database; store only references.

Avoid running performance tests on production databases.

Never connect development or test environments directly to production databases.

3. Column Design Guidelines

Choose the smallest appropriate data type; larger columns increase index size and I/O.

Convert strings to numeric forms when possible (e.g., store IP addresses as integers using INET_ATON and retrieve with INET_NTOA).

Prefer unsigned integers for non‑negative values to double the range.

Remember that VARCHAR(N) counts characters, not bytes; UTF8 stores three bytes per Chinese character, so VARCHAR(255) can hold 255 characters (≈765 bytes).

Avoid TEXT and BLOB types; if needed, place them in separate extension tables and avoid SELECT * on them.

Use ENUM sparingly; changing values requires ALTER and can degrade ORDER BY performance.

Define columns as NOT NULL to save index space and simplify comparisons.

Store timestamps with TIMESTAMP (4 bytes) or DATETIME (8 bytes); avoid storing dates as strings.

Financial amounts must use DECIMAL for precise arithmetic.

4. Index Design Standards

Limit each table to no more than five indexes; excessive indexes increase optimizer planning time.

Do not create separate indexes for every column; use composite indexes where appropriate.

Every InnoDB table must have a primary key; avoid using frequently updated, UUID, or large string columns as primary keys.

5. Common Index Column Recommendations

Index columns used in WHERE clauses of SELECT, UPDATE, or DELETE.

Index columns appearing in ORDER BY, GROUP BY, or DISTINCT.

Prefer composite indexes over multiple single‑column indexes for the above patterns.

Index columns used for join conditions.

6. Choosing Index Column Order

Place the most selective column (highest distinct‑value ratio) on the leftmost position of a composite index.

Place shorter columns first to maximize rows per page.

Place the most frequently used columns first.

7. Avoid Redundant and Duplicate Indexes

Duplicate example: PRIMARY KEY(id), INDEX(id), UNIQUE INDEX(id).

Redundant example: INDEX(a,b,c) together with INDEX(a,b) and INDEX(a).

8. Prefer Covering Indexes

Covering indexes contain all columns needed by a query, eliminating the need to fetch the row from the primary key and turning random I/O into sequential I/O.

9. Index SET Guidelines

Avoid foreign key constraints; instead, create indexes on the related columns and enforce referential integrity at the application level.

10. SQL Development Guidelines

Use prepared statements to reuse execution plans and prevent SQL injection.

Avoid implicit type conversion in queries; it can invalidate indexes.

Leverage existing indexes; avoid leading wildcards in LIKE patterns.

Only one column of a composite index can be used for a range query; place range columns to the right.

Prefer LEFT JOIN or NOT EXISTS over NOT IN.

Replace OR with IN (limit IN list to ~500 items).

Never use ORDER BY RAND(); generate random values in the application instead.

Avoid functions or calculations on indexed columns in WHERE clauses.

Prefer UNION ALL over UNION when duplicate rows are not a concern.

Split large, complex SQL statements into smaller ones to enable parallel execution.

11. Database Operation Behavior Guidelines

Batch large write operations (>1 million rows) to avoid long‑running transactions, binlog bloat, and lock contention.

Use tools like pt-online-schema-change for online schema changes on big tables.

Do not grant SUPER privileges to application accounts; reserve them for DBA use.

Apply the principle of least privilege: application accounts should have minimal rights and be confined to a single database.