52 Proven SQL Performance Optimization Techniques to Supercharge Your Queries

Overview

The guide presents fifty‑two actionable tips for optimizing SQL statements, primarily targeting MySQL but applicable to other relational databases. It covers index design, query formulation, schema choices, server settings, and maintenance procedures to reduce full‑table scans, improve concurrency, and lower resource consumption.

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 or -1) instead of NULL when appropriate.

Do not use != or <> operators; MySQL indexes only <, <=, =, >, >=, BETWEEN, IN and sometimes LIKE.

Avoid OR in WHERE clauses; replace with UNION ALL:

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 Avoid leading wildcards in LIKE patterns; use full‑text search for LIKE '%abc%' and keep LIKE 'abc%' when an index can be used.

Parameterised predicates can also trigger scans; rewrite to use indexed columns directly.

Avoid expressions or functions on indexed columns in WHERE clauses.

Prefer EXISTS over IN:

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

Indexes speed up SELECT but add overhead to INSERT / UPDATE; keep the total number of indexes per table under six and evaluate necessity for each.

Minimise updates to clustered index columns because they reorder physical storage.

Use numeric types for numeric data; avoid storing numbers as character strings.

Prefer VARCHAR / NVARCHAR over fixed‑length CHAR / NCHAR for variable‑length data.

Never use SELECT *; list only required columns.

Avoid returning excessively large result sets to clients unless truly needed.

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

Store intermediate results in temporary tables to avoid repeated scans and lock contention.

Apply NOLOCK only for read‑only queries that do not affect data integrity, following three safety rules.

Limit joins to five tables; consider temporary tables or table variables for complex queries.

Pre‑compute results and store them in lookup tables when possible.

Rewrite multiple OR conditions as separate queries combined with UNION ALL to leverage indexes.

Order values in IN lists by frequency, placing the most common first.

Push processing to the server (e.g., stored procedures) to reduce network overhead.

Configure thread count as max_connections + 5 when memory permits; otherwise keep it below max_connections.

Example of join order impact:

SELECT a.personMemberID, * FROM chineseresume a, personmember b WHERE a.personMemberID = b.referenceid AND a.personMemberID = 'JCNPRH39681'

Prefer EXISTS over COUNT(1) for existence checks; COUNT(1) scans the whole table.

Use >= instead of > when appropriate.

Index usage guidelines:

Align index creation with application query patterns; avoid more than six indexes on large OLTP tables.

Place highly selective columns first in composite indexes.

Periodically rebuild indexes and recompile stored procedures.

Remove unused indexes to keep execution plans optimal.

Avoid indexing columns with many duplicate values.

When a query with indexed columns runs slowly, it is often because the predicate applies a function or expression, forcing a scan. Rewrite such queries, e.g.: SELECT * FROM record WHERE card_no LIKE '5378%' Use batch INSERT or UPDATE for bulk data modifications.

Avoid loops in stored procedures; use set‑based operations instead.

Prefer UNION over subqueries for better optimizer handling.

Choose the driving table (the one with the fewest rows) as the last table in the FROM clause for rule‑based optimizers.

Filter unnecessary rows before GROUP BY to improve performance. Example of a faster query:

SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

Write SQL keywords in uppercase for readability and consistency.

Use short aliases for tables and columns to reduce parsing time.

Prevent deadlocks by accessing tables in a consistent order and keeping transactions short.

Prefer table variables over temporary tables for small intermediate datasets.

Minimise trigger usage; when necessary, follow best practices:

Do not fire the same trigger for multiple events.

Avoid transactional code inside triggers.

Prefer constraints over triggers when possible.

Index creation rules (summary):

Primary and foreign keys must be indexed.

Tables with >300 rows should have indexes on frequently queried columns.

Index high‑selectivity, small‑size columns; avoid indexing large text fields.

Use single‑column indexes when composite indexes offer no advantage.

Regularly clean up unused indexes.

MySQL query‑optimization checklist:

Enable slow‑query log and examine execution plans.

Avoid COUNT(*) on whole tables; use indexed columns when possible.

Prefer GROUP BY over DISTINCT when appropriate.

Ensure indexed columns are used in WHERE, GROUP BY, and ORDER BY.

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

Use INSERT ... ON DUPLICATE KEY or INSERT IGNORE to avoid a preceding SELECT before an insert.

Enable MySQL query cache for repeated identical queries.

Run EXPLAIN SELECT ... to see how MySQL processes a query and which indexes are used.

When only one row is needed, add LIMIT 1 to stop scanning after the first match.

Choose the appropriate storage engine:

MyISAM – read‑heavy, few writes, low transaction needs.

InnoDB – transactional, high concurrency, frequent updates.

Optimize data types: use the smallest suitable type, avoid NULL, and prefer MEDIUMINT over BIGINT when possible.

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

Select appropriate string types: CHAR for fixed length, VARCHAR for variable length, and avoid overly large TEXT unless necessary.

Any operation on a column (functions, calculations, expressions) forces a table scan; keep such operations on the right side of the equality.

Backup and Maintenance

Backup from a secondary replica to avoid impacting the primary.

Stop replication during backup to maintain consistency.

Shut down MySQL and copy database files directly for a cold backup.

If using mysqldump, also back up binary logs to preserve replication continuity.

Avoid LVM snapshots as they may produce inconsistent data.

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

Use mysqldump --opt for optimal dump performance.

Analyze and optimise tables before each backup.

Temporarily disable foreign‑key checks during import to speed up loading.

Temporarily disable unique‑key checks during import when safe.

After each backup, record database, table, and index sizes for growth monitoring.

Automate monitoring of replica errors and lag with scheduled scripts.

Perform regular backups on a defined schedule.