Common SQL Optimization Techniques for MySQL

1. Review of MySQL Execution Process – The client sends a query, MySQL checks the cache, parses the SQL into a parse tree, performs pre‑processing (checking tables, columns, permissions), generates an execution plan, accesses the storage engine, and finally returns the result (often cached).

2. Common SQL Performance Optimization Strategies

2.1 Create indexes on columns used in WHERE and ORDER BY – Indexes avoid full‑table scans and dramatically speed up lookups.

2.2 Use default values instead of NULL in WHERE – Replacing NULL with a sentinel value (e.g., 0 or -1) helps the optimizer use indexes.

2.3 Avoid != or <> operators – These often force a full‑table scan; prefer = , < , > , BETWEEN , IN , or appropriate LIKE patterns.

2.4 Avoid OR in WHERE – OR can invalidate indexes; rewrite using UNION ALL when possible:

SELECT id FROM t WHERE num=10 UNION ALL SELECT id FROM t WHERE num=20

2.5 Use IN sparingly; prefer BETWEEN for ranges – For consecutive numeric values, BETWEEN is more efficient.

2.6 Avoid left‑wildcard LIKE '%…' – Right‑wildcard LIKE '…%' can use an index; left‑wildcard requires full scans or special techniques (reverse index, external search engines).

2.7 Parameterized WHERE conditions may cause scans – Use index hints when necessary, e.g., SELECT id FROM t WITH (INDEX(index_name)) WHERE num=@num .

2.8 Prefer EXISTS over IN – Example replacement:

SELECT num FROM a WHERE EXISTS (SELECT 1 FROM b WHERE num=a.num)

2.9 Limit the number of indexes – Too many indexes slow down INSERT / UPDATE ; keep indexes to essential columns (usually ≤6 per table).

2.10 Use numeric columns when possible – Numeric comparisons are faster and consume less storage than string comparisons.

2.11 Prefer VARCHAR / NVARCHAR over fixed‑length CHAR / NCHAR – Variable‑length fields save space and improve query speed.

2.12 Avoid SELECT * – List only required columns to reduce I/O, CPU, and network overhead.

2.13 Pre‑compute frequently used results – Store derived data in tables instead of calculating on the fly.

2.14 Order frequent values first in IN lists – Improves evaluation efficiency.

2.15 Use EXISTS instead of COUNT(1) to test existence

2.16 Batch inserts/updates – Combine multiple rows in a single statement:

INSERT INTO user (id, username) VALUES (1,'xx'), (2,'yy');

2.17 Filter rows before GROUP BY – Reduces the amount of data the engine must aggregate.

2.18 Avoid deadlocks – Access tables in a consistent order, keep transactions short, and never wait for user input inside a transaction.

2.19 Index creation guidelines – Primary/foreign keys must be indexed; tables >300 rows should have indexes on frequently joined or filtered columns; prefer high‑selectivity, small, and single‑column indexes; limit composite indexes to ≤3 columns and only when those columns appear together in queries.

2.20 Minimize unnecessary whitespace in SQL statements – Excess spaces are not trimmed by the query cache and can affect parsing.

2.21 Assign an ID primary key (INT UNSIGNED AUTO_INCREMENT) to every table

2.22 Analyze execution plans with EXPLAIN – Understand type hierarchy (system, const, eq_ref, ref, range, index, all) and Extra information (Using index, Using where, Using temporary).

2.23 Use LIMIT 1 when only one row is needed – Stops the engine after the first match.

2.24 Split large DELETE / UPDATE / INSERT statements into smaller batches

2.25 Partition tables to keep single‑table size manageable (≈5 million rows) – Use archiving, sharding, or partitioning to avoid performance degradation.

Following these practices helps keep MySQL databases stable, efficient, and easier to maintain.