Essential MySQL Optimization Checklist: 46 Proven Practices to Boost Performance

MySQL Execution Process

When a client sends a query, MySQL first checks the query cache. If the cache misses, the server parses the SQL into a parse tree, validates syntax, performs pre‑processing (checking object existence and permissions), generates an execution plan, invokes the appropriate storage engine, and returns the result while updating the cache.

Database Naming Conventions

Use lowercase letters and underscores for all identifiers.

Avoid MySQL reserved keywords.

Names should be meaningful and no longer than 32 characters.

Temporary tables: tmp_YYYYMMDD; backup tables: bak_YYYYMMDDHHMMSS.

Columns that store identical data must have the same name and type across tables.

Storage Engine

Prefer the InnoDB engine for all tables unless a feature (e.g., column‑store) requires MyISAM. InnoDB provides transactions, row‑level locking, better crash recovery, and superior performance under high concurrency.

Primary Key Requirements

Every InnoDB table should have a primary key. Choose a non‑changing, narrow column (typically an auto‑increment integer). Avoid multi‑column primary keys, UUIDs, MD5 hashes, or other non‑sequential values.

Character Set

Set the database and table charset to utf8mb4 (or utf8 if emojis are not needed) to avoid conversion issues and index inefficiencies.

SQL Writing Guidelines

Avoid SELECT * : specify only needed columns to reduce CPU, I/O, memory, and network usage and to enable covering indexes.

Do not use OR in WHERE : it often disables index usage; rewrite with UNION or separate predicates.

Prefer numeric types over strings for identifiers and status codes because numeric comparison is a single‑step operation.

Use VARCHAR instead of CHAR for variable‑length data to save space.

Use DECIMAL for monetary values to retain exact precision (4 bytes per 9 digits, plus one byte for the decimal point).

Avoid ENUM : altering values requires ALTER TABLE, and ordering is slower.

Minimize columns in DISTINCT operations because they add CPU overhead.

Replace NULL checks with default values when possible; this helps the optimizer keep indexes.

Avoid != or <> in WHERE clauses as they frequently invalidate indexes.

Prefer INNER JOIN over LEFT/RIGHT JOIN when the result set is the same; keep the left table small for left joins.

GROUP BY Optimization

Filter rows before grouping to reduce the amount of data that must be aggregated.

SELECT job, AVG(salary)
FROM employee
WHERE job='develop' OR job='test'
GROUP BY job;

TRUNCATE vs DELETE

Use TRUNCATE TABLE to quickly remove all rows; it deallocates data pages and logs only the page drops. It cannot be used on tables referenced by foreign keys.

DELETE/UPDATE Batching

Add LIMIT or process rows in batches to avoid accidental full‑table deletions, reduce lock time, and keep transactions short.

UNION vs UNION ALL

Use UNION ALL when duplicate rows are unlikely, as it simply concatenates result sets without sorting.

SELECT username, tel FROM user
UNION ALL
SELECT departmentname FROM department;

IN List Size

Keep IN lists small; large lists are sorted in memory and can be replaced with BETWEEN or a join to a temporary table.

Batch Inserts

INSERT INTO user (id, username) VALUES
  (1, '哪吒编程'),
  (2, '妲己');

Join and Index Limits

Limit the number of joined tables and indexes to roughly five each. Excessive joins create temporary tables and increase compilation cost; too many indexes degrade write performance.

Index Design

Do not index every column; each index adds storage overhead and slows inserts/updates.

Order columns in a composite index by selectivity, then by length, then by frequency of use.

Use covering indexes that contain all columns referenced by SELECT, WHERE, ORDER BY, and GROUP BY to avoid back‑table lookups.

Prepared Statements

Prepared statements reuse execution plans, reduce parsing overhead, and protect against SQL injection.

Avoid Large Transactions

Split massive write batches into smaller transactions to prevent long‑running locks and timeout errors.

Avoid Functions on Indexed Columns

Applying functions (e.g., LOWER(col)) disables index usage.

Composite Index Examples

CREATE INDEX idx_username_tel ON user(deptid, position, createtime);
SELECT username, tel FROM user
WHERE deptid = 1 AND position = 'java开发'
ORDER BY deptid, position, createtime DESC;

Left‑most Prefix Rule

ALTER TABLE employee ADD INDEX idx_name_salary (name, salary);
SELECT * FROM employee WHERE name='哪吒编程';

If the leftmost column of a composite index is not used, the index cannot be applied.

FORCE INDEX

When the optimizer chooses a suboptimal index, use FORCE INDEX (index_name) to direct it.

LIKE Optimization

Prefer right‑anchored patterns ( LIKE 'prefix%') to use indexes.

Left‑anchored patterns ( LIKE '%suffix') require reverse indexing or full scans.

Full wildcards ( LIKE '%term%') cannot be indexed; consider a dedicated search engine.

SQL Style Consistency

Maintain consistent case and formatting; this improves readability and increases the chance that the optimizer reuses execution plans.

Complexity Management

Avoid deeply nested queries (>3 levels) and overly long statements; they increase planning time and may produce suboptimal plans.

Chunk Large DML

Break massive DELETE, UPDATE, or INSERT statements into smaller batches to reduce lock contention and improve throughput.

Temporary Table Best Practices

Avoid frequent create/drop cycles.

For large inserts, use SELECT INTO instead of CREATE TABLE + INSERT.

Explicitly drop temporary tables at the end of stored procedures.

EXPLAIN Analysis

Key type values (from best to worst): system, const, eq_ref, ref, range, index, all. Aim for ref or range. Pay attention to Extra fields such as Using index, Using where, and Using temporary.

Read‑Write Splitting & Sharding

When a single server cannot handle the load, combine read‑write separation with database/table sharding, often together with caching.

Efficient Pagination

Instead of LIMIT offset, count on large tables, remember the last retrieved id and query WHERE id > last_id LIMIT count.

Table Size Management

Keep single table row count ≤ 5 million. Archive old data or shard when tables grow larger to simplify schema changes, backups, and restores.

Partitioning Caution

Partitions appear as separate files but behave as one logical table.

Select partition keys wisely; cross‑partition queries can be slower.

Physical sharding is often preferable for very large datasets.

Hot/Cold Data Separation

Move rarely accessed columns to auxiliary tables to reduce table width and improve cache hit rates.

Avoid Placeholder Columns

Unnamed placeholder columns are ambiguous, hard to type, and require costly schema changes.

Large Binary Data

Store files, images, and other large blobs on a file server; keep only their paths in the database.

TEXT/BLOB Handling

Place TEXT / BLOB columns in separate extension tables.

When selecting, avoid SELECT * to prevent unnecessary I/O.

Use prefix indexes for TEXT / BLOB columns because MySQL limits index length; these columns cannot have default values.