52 Practical SQL Query Performance Optimization Strategies

This article compiles fifty‑two practical strategies for optimizing SQL queries, primarily targeting MySQL but applicable to most relational databases.

Index usage : Create indexes on columns used in WHERE and ORDER BY clauses, avoid full‑table scans, limit the number of indexes (preferably no more than six per large OLTP table), and ensure indexes are on selective, small, non‑NULL columns.

Avoid costly predicates : Do not use != or <>, avoid functions and expressions on indexed columns in WHERE, and replace OR conditions with UNION ALL when possible, e.g.,

select id from t where num=10 union all select id from t where num=20

.

IN / NOT IN : Prefer BETWEEN over IN for continuous ranges, and use EXISTS instead of IN when appropriate, e.g.,

select num from a where exists (select 1 from b where num=a.num)

.

LIKE patterns : Use left‑anchored patterns ( LIKE 'abc%') to enable index usage; avoid leading wildcards ( LIKE '%abc') which force full scans.

Temporary tables : Store intermediate results in temporary tables to reduce repeated scans and lock contention; prefer table variables for small, in‑memory data.

NOLOCK hint : Apply only to read‑only queries that do not modify data, and avoid on tables with frequent page splits; consider alternatives before using it.

Query ordering and joins : Place the smallest or most selective table first in the FROM clause, limit the number of joins (<5), and use table aliases to simplify parsing.

GROUP BY optimization : Filter unnecessary rows before grouping; rewrite queries to push predicates into the WHERE clause.

Server configuration : Set thread count to max_connections + 5 when memory permits, otherwise keep it below max_connections; enable query cache for repeated reads.

Backup and maintenance : Use replica servers for backups, stop replication during backup, employ mysqldump --opt, disable foreign‑key checks and unique checks during bulk loads, and regularly rebuild indexes.

Data types : Choose the smallest suitable type (e.g., MEDIUMINT instead of BIGINT), avoid NULL where possible, use ENUM for fixed sets, and prefer TIMESTAMP over DATETIME when the range is sufficient.

Storage engine selection : Use InnoDB for transactional workloads and MyISAM only for read‑heavy, low‑concurrency scenarios.

Additional tips : Use EXPLAIN to analyze execution plans, apply LIMIT 1 when only one row is needed, and avoid excessive triggers or complex stored‑procedure loops.