52 Proven SQL Performance Optimization Techniques You Must Use

This guide presents a comprehensive list of 52 SQL performance‑optimization tactics, focusing on MySQL but applicable to most relational databases.

Index and Query Design

Avoid full‑table scans by creating indexes on columns used in WHERE and ORDER BY clauses.

Prefer NOT NULL over nullable columns; use sentinel values (e.g., 0, -1) instead of NULL checks.

Avoid != or <> in WHERE; MySQL indexes only support <, <=, =, >, >=, BETWEEN, IN and some LIKE patterns.

Replace OR conditions with UNION ALL to keep index usage, e.g.,

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

.

Prefer BETWEEN over IN for continuous numeric ranges: select id from t where num between 1 and 3.

Leading wildcards in LIKE ( '%abc%') force table scans; use prefix searches ( 'abc%') or full‑text indexes instead.

Avoid functions or expressions on indexed columns in WHERE clauses, as they prevent index usage.

Use EXISTS instead of IN for sub‑queries:

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

.

Limit the number of indexes per table (generally ≤ 6) and keep them on high‑selectivity, small, numeric columns.

Do not index columns with low selectivity or large text fields; avoid over‑indexing composite keys unless all columns are frequently used together.

SQL Writing Practices

Never use SELECT *; list only required columns.

Use table aliases to shorten column references and reduce parsing time.

Prefer temporary tables or table variables over excessive joins; limit joins to ≤ 5 tables.

Filter rows before GROUP BY to reduce work, e.g., move predicates to WHERE instead of HAVING.

Write GROUP BY queries with filters first for better performance.

Write SQL statements in uppercase for readability; Oracle parses uppercase internally.

Use LIMIT 1 when only a single row is needed to stop the engine early.

Server and Configuration Tips

Set thread count to max_connections + 5 when memory permits; otherwise keep it below max_connections.

Use USE INDEX to force a better index when the optimizer picks a sub‑optimal one.

Enable the MySQL query cache for repeated queries.

Monitor slow queries with the slow‑query log and analyze execution plans with EXPLAIN.

During bulk inserts/updates, use batch operations instead of row‑by‑row statements.

In stored procedures, set SET NOCOUNT ON to reduce network chatter.

Backup and Maintenance

Backup from a replica to avoid impacting the primary.

Stop replication during backup to ensure consistency.

Use mysqldump --opt and disable foreign‑key checks during import.

Periodically rebuild indexes and recompile stored procedures.

Track database size growth after each backup.

Data Types and Schema Design

Prefer numeric types over character types for numeric data.

Use VARCHAR instead of CHAR for variable‑length strings; keep field lengths as small as possible.

Choose DATETIME (8 bytes) or TIMESTAMP (4 bytes) appropriately; TIMESTAMP is ideal for update timestamps.

Define primary keys as unsigned INT with AUTO_INCREMENT.

Consider ENUM for low‑cardinality text fields (e.g., gender, province) to improve speed.

Avoid excessive CHAR(255) definitions; use the smallest suitable size.

Examples of Problematic Queries and Fixes

Problematic queries that caused seconds‑long scans:

SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378'   -- 13 s</code>
<code>SELECT * FROM record WHERE amount/30 < 1000               -- 11 s</code>
<code>SELECT * FROM record WHERE convert(char(10), date, 112) = '19991201' -- 10 s

Rewritten versions that use indexes and run in < 1 s:

SELECT * FROM record WHERE card_no LIKE '5378%'</code>
<code>SELECT * FROM record WHERE amount < 1000*30</code>
<code>SELECT * FROM record WHERE date = '1999/12/01'

Storage Engine Choice

MyISAM : Best for read‑heavy workloads with few updates; low transaction requirements.

InnoDB : Supports transactions, row‑level locking, and high concurrency; ensure AUTOCOMMIT is managed to avoid per‑statement commits.

By applying these 52 tips—ranging from index strategy and query rewriting to server configuration and backup practices—developers can significantly improve the performance and scalability of their SQL databases.