52 Proven SQL Performance Optimization Techniques to Supercharge Your Queries

This article lists 52 practical SQL performance‑optimization techniques for MySQL and SQL Server. The tips cover index design, query rewriting, temporary structures, server configuration, backup procedures, and schema choices. Applying them can reduce execution time, lower resource usage, and improve concurrency.

Index‑related optimizations

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

Define columns as NOT NULL whenever possible; use sentinel values (e.g., 0, -1) instead of NULL to simplify indexing.

Do not use != or <> in indexed predicates; MySQL indexes support <, <=, =, >, >=, BETWEEN, IN and sometimes LIKE.

Replace OR conditions with UNION ALL to preserve 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 consecutive numeric ranges, e.g. SELECT id FROM t WHERE num BETWEEN 1 AND 3; Leading‑wildcard LIKE '%abc%' forces a scan; use full‑text search or a trailing wildcard LIKE 'abc%' instead.

Parameterized predicates that apply functions to indexed columns can disable index usage; rewrite them to use the column directly.

Avoid expressions or functions on indexed columns in WHERE clauses (e.g., SUBSTRING(col,1,4)='1234'); rewrite as col LIKE '1234%'.

Prefer EXISTS to IN for sub‑queries:

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

Limit the number of indexes per table (ideally ≤ 6) and avoid indexing low‑selectivity or large text columns.

Minimize updates to clustered index columns; changing them forces row reordering.

Store numeric data in numeric types; avoid storing numbers as strings.

Prefer VARCHAR / NVARCHAR over fixed‑length CHAR / NCHAR to save space and improve search speed.

Never use SELECT *; list only required columns.

Paginate or filter results to avoid returning excessively large result sets.

Query structuring and execution tricks

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

Store intermediate results in temporary tables (or table variables) to avoid repeated scans and reduce lock contention.

Apply NOLOCK (or READ UNCOMMITTED) only for read‑only queries that can tolerate dirty reads; never on DML statements.

When using NOLOCK, follow three rules: (a) do not apply to queries that modify data, (b) avoid on tables with frequent page splits, (c) prefer temporary tables over NOLOCK for concurrency.

Break complex OR conditions into separate queries combined with UNION ALL when indexes are not used.

Place the most frequent values first in an IN list to reduce comparisons.

Push data‑processing logic into stored procedures to minimise network round‑trips.

Pre‑compute lookup tables for frequently used derived data.

Rewrite function‑based predicates, e.g. replace WHERE SUBSTRING(card_no,1,4)='5378' with WHERE card_no LIKE '5378%' (execution drops from seconds to < 1 s).

Batch INSERT / UPDATE statements instead of row‑by‑row processing.

Avoid loops in stored procedures; use set‑based statements.

Prefer >= over > when the boundary value is inclusive.

Index creation and maintenance guidelines

Create indexes that match the most common query patterns; keep the total number of indexes per large OLTP table ≤ 6.

Index columns used in WHERE, joins, and primary/foreign keys.

For composite indexes, the leftmost column must appear in the query predicate; otherwise the index is ignored.

Periodically rebuild fragmented indexes and recompile stored procedures to keep execution plans optimal.

Drop unused indexes to reduce write overhead and keep the optimizer’s plan cache clean.

Avoid indexing columns with many duplicate values (low selectivity).

If a composite index contains more than three columns, reconsider its necessity; a single‑column index may be sufficient.

Prefer single‑column indexes unless queries consistently use all columns of a composite index together.

Server and engine configuration

Set the thread pool size to max_connections + 5 if memory permits; otherwise keep it ≤ max_connections.

Order joins so that the driving table (fewest rows) appears last in the FROM clause (Oracle rule).

Use LIMIT 1 when only a single row is needed to stop the engine after the first match.

Choose the appropriate storage engine: MyISAM for read‑heavy workloads with few writes; InnoDB for transactional consistency and high concurrency.

For InnoDB, wrap related statements in an explicit transaction (disable autocommit) to avoid per‑statement commits.

Define an unsigned INT AUTO_INCREMENT primary key on every table.

Enable the query cache for repeated identical queries (MySQL 5.6 and earlier).

Use EXPLAIN to inspect execution plans and verify index usage.

Apply USE INDEX when the optimizer picks a sub‑optimal index.

Backup and maintenance practices

Back up from a secondary replica to avoid impacting the primary.

Stop replication during backup to maintain consistency.

Use mysqldump --opt and also back up binary logs.

Avoid relying on LVM snapshots for MySQL backups; they can produce inconsistent data.

Export data table‑by‑table for easier single‑table restores.

Temporarily disable foreign‑key and unique‑key checks during bulk imports.

Record database, table, and index sizes after each backup to monitor growth.

Automate monitoring of replication lag and errors.

Schedule regular backups (daily or according to RPO requirements).

Data‑type and schema recommendations

Prefer smaller integer types (e.g., MEDIUMINT instead of INT) and avoid NULL where possible.

Use DATETIME (8 bytes) or TIMESTAMP (4 bytes) appropriately; TIMESTAMP is ideal for update timestamps (1970‑2037).

Define character fields with the minimal length needed (e.g., CHAR(6) for zip codes) rather than large CHAR(255) or unnecessary VARCHAR.

Consider ENUM for low‑cardinality text fields such as gender or province; MySQL stores them as numeric values, which are faster to compare.

Choose the most compact data type for each column to reduce storage and improve query speed.

Illustrative rewrites

Queries that apply functions to indexed columns cause full scans:

SELECT * FROM record WHERE SUBSTRING(card_no,1,4)='5378';  -- 13 s

Rewrite to use a prefix LIKE so the index can be used:

SELECT * FROM record WHERE card_no LIKE '5378%';  -- <1 s

Similarly, avoid arithmetic in predicates:

SELECT * FROM record WHERE amount/30 < 1000;  -- 11 s

Rewrite as:

SELECT * FROM record WHERE amount < 1000*30;  -- <1 s

And for date conversion:

SELECT * FROM record WHERE CONVERT(char(10),date,112)='19991201';  -- 10 s

Rewrite as:

SELECT * FROM record WHERE date = '1999-12-01';  -- <1 s

These rewrites illustrate how moving calculations from the WHERE clause to the constant side enables index usage and dramatically improves performance.

Additional best practices

Use LIMIT when only a subset of rows is needed.

Keep GROUP BY efficient by filtering rows before aggregation.

Write SQL keywords in uppercase for readability; the parser is case‑insensitive.

Avoid deadlocks by accessing tables in a consistent order within transactions.

Prefer table variables over temporary tables for small intermediate result sets (they reside in memory).

Minimize the use of triggers; use constraints or application logic when possible.

Set SET NOCOUNT ON at the start of stored procedures (SQL Server) to reduce network chatter.

Enable query caching (MySQL) for identical repeated queries.

Use EXPLAIN regularly to verify that the optimizer chooses the intended indexes.