Comprehensive Guide to MySQL SQL Optimization Techniques

SQL optimization is a critical metric for evaluating a developer's skill and a low‑cost, high‑impact method for improving system performance, especially in large‑scale applications.

Cost Hierarchy

Hardware > System Configuration > Table Structure > SQL & Indexes. Optimizing SQL and indexes yields the most noticeable performance gains.

Five Core Principles for MySQL Layer Optimization

Reduce data access : Choose appropriate field types, enable compression, and use indexes to minimize disk I/O.

Return less data : Select only required columns and use pagination.

Reduce interaction count : Batch DML operations and use stored procedures.

Reduce CPU overhead : Avoid full‑table scans, sorting, and large memory usage.

Leverage more resources : Use table partitioning for parallelism.

Key SQL Optimization Rules

Avoid leading LIKE '%value' patterns; place the wildcard at the end (e.g., LIKE 'value%') or use INSTR / full‑text indexes.

Replace IN (a,b) with BETWEEN a AND b when dealing with continuous ranges.

Substitute OR conditions with UNION to enable index usage.

Do not test for NULL directly; use default values (e.g., WHERE score = 0).

Never place functions or expressions on the left side of a comparison; move them to the right side (e.g., WHERE score = 10*9).

Avoid the dummy condition WHERE 1=1 in dynamically built queries.

Do not use <> or != on indexed columns; redesign the index if inequality is required.

When using composite indexes, always include the leftmost column in the WHERE clause.

Prevent implicit type conversion by matching literal types to column types.

Ensure ORDER BY columns are part of the WHERE filter to allow index‑based sorting.

Use index hints ( USE INDEX, IGNORE INDEX, FORCE INDEX) sparingly and only when the optimizer chooses a sub‑optimal plan.

DML Optimization

Batch multiple INSERT values in a single statement to reduce parsing overhead, network round‑trips, and connection usage: INSERT INTO T VALUES (1,2),(1,3),(1,4); Commit periodically to release undo and redo logs and reduce lock contention, especially for large DELETE operations.

Use user‑defined variables to retrieve values without a second query, e.g.:

UPDATE t1 SET time = NOW() WHERE col1 = 1 AND @now := NOW();
SELECT @now;

Query Scheduling and Priority

MySQL supports LOW_PRIORITY and HIGH_PRIORITY modifiers and the --low-priority-updates server option to control the order of reads and writes for MyISAM, MEMORY, and MERGE tables.

Pagination Strategies

Standard offset pagination ( LIMIT offset, count) becomes slower on deep pages. Use a covering index to fetch primary keys first and then join to retrieve full rows:

SELECT t.* FROM (SELECT id FROM t WHERE thread_id=10000 AND deleted=0 ORDER BY gmt_create ASC LIMIT 0,15) a JOIN t ON a.id = t.id;

Table Design Recommendations

Create indexes on columns used in WHERE and ORDER BY clauses.

Prefer numeric types over character types for enumerations to reduce storage and improve comparison speed.

Use VARCHAR instead of CHAR to save space; NULL values in VARCHAR consume no storage.

For large tables, split queries into smaller batches or use temporary tables to handle complex results.

Use TRUNCATE instead of DELETE when removing all rows to avoid undo logging and to reset auto‑increment counters.

By following these guidelines, developers can achieve significant performance improvements without additional hardware costs.