MySQL Performance Optimization Guidelines from a Senior Architect

1. Understand MySQL execution process – The client sends a query, MySQL checks cache, parses the SQL into a parse tree, performs pre‑processing, generates an execution plan, accesses the storage engine, and returns results to the client.

2. Naming conventions – Use lowercase with underscores, avoid reserved keywords, keep names meaningful and under 32 characters, prefix temporary tables with tmp_ and backup tables with bak_ , and ensure columns storing the same data have identical names and types.

3. Storage engine and primary key – Prefer InnoDB for all tables (unless a special engine is required). Every InnoDB table must have a primary key; avoid frequently updated columns or UUIDs as primary keys and use auto‑increment integers instead.

4. Character set – Use UTF8 (or utf8mb4 for emoji) for all databases and tables to avoid conversion overhead and index invalidation.

5. Query writing best practices

Avoid SELECT * ; specify needed columns.

Do not use OR in WHERE clauses as it often disables indexes.

Prefer INNER JOIN when result sets are identical; keep the left table small for LEFT JOIN .

Filter rows before grouping: use WHERE then GROUP BY instead of the opposite.

Use TRUNCATE TABLE instead of DELETE for full‑table clears when possible.

Apply LIMIT (or batch deletes) to reduce lock time and CPU load.

Replace UNION with UNION ALL when duplicate removal is unnecessary.

Keep the number of columns in IN clauses reasonable; consider BETWEEN or joins for large sets.

6. Index usage

Limit total indexes per table to about five; excessive indexes hurt insert/update performance.

Do not create an index on every column; only index columns used in filters, joins, sorting, or covering queries.

When designing composite indexes, place the most selective and shortest columns on the leftmost side.

Use covering indexes (include all queried columns) to avoid secondary‑index lookups.

Force a specific index with FORCE INDEX if the optimizer chooses a sub‑optimal one.

Avoid functions on indexed columns; they render the index ineffective.

Follow the left‑most rule for composite indexes; partial leftmost prefixes still use the index.

7. Prepared statements – Use pre‑compiled statements to reuse execution plans and prevent SQL injection.

8. Transaction and batch handling – Break large DELETE / UPDATE / INSERT operations into smaller batches, use LIMIT 1 when only one row is needed, and avoid long‑running transactions that lock many rows.

9. Partitioning and table size – For very large tables, consider read/write splitting, sharding, or archiving hot data; keep single table size under ~5 million rows for manageable maintenance.

10. BLOB/TEXT handling – Store large binary data outside the database; if BLOB/TEXT columns are required, place them in a separate extension table and avoid SELECT * on the main table.

11. Miscellaneous tips

Use EXPLAIN to analyze query plans; aim for ref or range access types.

Prefer right‑anchored LIKE 'prefix%' patterns; left‑anchored patterns need reverse indexes or full‑text search.

Maintain consistent SQL style, comment tables/columns, and set columns to NOT NULL when possible.

Avoid excessive nesting (>3 levels) and overly complex statements; simpler SQL is more likely to be cached.

Use appropriate pagination (e.g., “where id > last_id limit 20”) instead of large offsets.