Master MySQL Performance: 12 Proven SQL Optimization Techniques

MySQL Internal Components

MySQL processes a query through several stages: the connector establishes client connections and permissions; the query cache (disabled by default and removed in MySQL 8.0) attempts to serve results from memory; the parser creates an abstract syntax tree; the optimizer uses statistics and the data dictionary to generate an execution plan, choosing indexes when possible; finally, the executor runs the plan via storage‑engine APIs.

SQL Statement Optimization Techniques

Avoid subqueries : rewrite them as joins. Example: SELECT * FROM t1 JOIN t2 ON t1.id = t2.id; Subqueries in MySQL 5.5 often execute the outer table first, causing severe slowdown on large datasets.

Replace OR with IN for equality checks. Example: SELECT * FROM t WHERE id IN (10,20,30); MySQL optimizes IN by storing constants in a sorted array.

Efficient pagination : large LIMIT m,n offsets force MySQL to read and discard rows. Use the maximum id from the previous page:

SELECT id, name FROM t WHERE id > 866612 LIMIT 20;

Minimize ORDER BY : omit sorting when not required; for GROUP BY without ordering, add ORDER BY NULL.

SELECT goods_id, COUNT(*) FROM t GROUP BY goods_id ORDER BY NULL;

Prefer UNION ALL over UNION when duplicate rows are impossible, avoiding the extra sorting and deduplication step.

Avoid random row selection with ORDER BY RAND() as it disables index use. Use deterministic range queries instead.

Batch INSERT multiple rows in a single statement:

INSERT INTO t (id, name) VALUES (1,'aaa'),(2,'bbb'),(3,'ccc');

Select only needed columns instead of SELECT *. This reduces CPU, I/O, memory, and network usage and enables covering indexes.

Distinguish IN vs EXISTS : IN is efficient when the outer table is large and the subquery returns a small set; EXISTS is better when the outer table is small.

Optimize GROUP BY : use ORDER BY NULL, ensure the type is at least range, increase tmp_table_size for in‑memory temporary tables, or hint SQL_BIG_RESULT for disk‑based processing.

Join Algorithms and Buffer Tuning

MySQL chooses a join algorithm based on indexes and table sizes:

Index Nested‑Loop Join (NLJ) : used when the driven table has an index. The driver reads rows, looks up matching rows via the index.

Block Nested‑Loop Join (BLJ) : used when the driven table lacks an index; rows are read into join_buffer in blocks to reduce I/O.

Simple Nested‑Loop Join (SLJ) : full table scans for each driver row.

Check the join_buffer_size variable to ensure enough memory for block joins: SHOW VARIABLES LIKE 'join_buffer_size'; Prefer driving the smaller result set and index the join columns of the driven table. Use STRAIGHT_JOIN to force the left table as driver, or choose INNER JOIN which automatically selects the smaller table.

Index Optimization and Avoiding Index Loss

Leftmost prefix rule : multi‑column indexes are used only from the leftmost column onward.

No functions on indexed columns : expressions like WHERE id + 1 = 10 prevent index use; rewrite as WHERE id = 9.

Avoid type casting that disables indexes : comparing a numeric column to a string forces MySQL to cast, breaking the index.

Covering indexes : include all selected columns in the index so the engine can satisfy the query from the index alone.

Negative conditions ( !=, NOT IN, NOT LIKE) often cause full table scans.

IS NULL / IS NOT NULL historically prevents index use; newer MySQL versions have improved this.

LIKE with leading wildcard ( %abc) disables the index; place the wildcard at the end ( abc%) whenever possible.

Minimize OR : all columns involved in an OR must be indexed; otherwise MySQL falls back to a full scan.

Composite index ordering : put high‑selectivity columns first to maximize discrimination.

Prefix indexes for long VARCHAR columns reduce index size and I/O, though they cannot support ORDER BY or GROUP BY.

MySQL 8.0 also supports functional indexes, allowing indexes on expressions such as LEN(name):

ALTER TABLE t_user ADD KEY idx_name_len ((LENGTH(name)));

Additional Performance Tips

Use numeric columns for fields that store numbers; integer comparisons are faster than string comparisons.

When possible, store redundant data to reduce join complexity.

Upgrade storage to SSDs to lower I/O latency.

For large result sets, prefer INNER JOIN over LEFT JOIN and ensure the right‑hand table has appropriate indexes.