52 Proven SQL Optimization Techniques to Supercharge Your Queries

This guide presents a comprehensive collection of 52 SQL performance‑optimization tactics that cover everything from index design to query rewriting, locking strategies, backup procedures, and data‑type choices.

Indexing Best Practices

Create indexes on columns used in WHERE and ORDER BY clauses; keep the total number of indexes per large OLTP table under six.

Prefer numeric fields over character fields for better comparison speed.

Use VARCHAR / NVARCHAR instead of fixed‑length CHAR / NCHAR to save space and improve search efficiency.

Avoid indexing low‑selectivity or very large text columns; index only high‑selectivity, small fields.

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

Periodically rebuild indexes and recompile stored procedures.

Do not over‑index tables that experience frequent inserts/updates; excess indexes increase write overhead.

Remove unused indexes to keep execution plans optimal.

Query‑Writing Tips

Avoid full‑table scans by using appropriate indexes; never use SELECT *, list needed columns explicitly.

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

Replace OR conditions with UNION ALL when possible to preserve index usage.

Prefer IN with a short list or BETWEEN for continuous ranges; avoid IN / NOT IN on large sets.

Use EXISTS instead of IN for sub‑queries, e.g.:

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

Write LIKE 'prefix%' instead of LIKE '%suffix' to enable index usage.

Prefer >= over > when appropriate.

Filter rows before GROUP BY to reduce the data set.

When possible, rewrite expressions so that column operations occur on the right side of the equality, allowing the optimizer to use indexes.

Join and Table Order Strategies

Limit joins to five tables; use temporary tables or table variables for intermediate results.

Choose the driving table (the one with the fewest rows) as the last table in the FROM clause for Oracle; for three or more tables, use the intersection table referenced by others.

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

Temporary Tables and Set‑Based Operations

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

Prefer table variables over temporary tables when the data fits in memory, as they are faster.

Avoid using triggers unless constraints cannot achieve the same logic; triggers add overhead and can cause deadlocks.

Locking and Transaction Guidelines

Use NOLOCK only for read‑only queries that do not affect inserts/updates/deletes and when the table does not suffer frequent page splits.

Access tables in a consistent order across stored procedures and triggers to prevent deadlocks.

Keep transactions short and avoid user interaction inside a transaction.

Group‑By and Aggregation Optimizations

Apply filters before GROUP BY to reduce the number of rows processed.

Prefer moving the WHERE clause before the GROUP BY rather than using HAVING for non‑aggregated conditions.

Storage Engine Selection

MyISAM : suitable for read‑heavy workloads with few writes and low concurrency requirements.

InnoDB : provides ACID transactions, row‑level locking, and better performance for mixed read/write workloads; disable autocommit and batch statements to improve speed.

Data‑Type and Column Design

Choose the smallest appropriate numeric type (e.g., MEDIUMINT instead of INT); set columns to NOT NULL and avoid NULL where possible.

Use ENUM for low‑cardinality text fields like gender or province.

Prefer CHAR(6) for fixed‑length codes instead of CHAR(255) or overly large VARCHAR columns.

Store timestamps with TIMESTAMP (4 bytes) rather than DATETIME (8 bytes) when the range 1970‑2037 is sufficient.

Backup and Maintenance Practices

Perform backups from a secondary replica to avoid data inconsistency.

Stop replication during backup, or shut down MySQL and copy raw files.

When using mysqldump, include --opt and also dump binary logs.

Disable foreign‑key checks and unique‑key checks during bulk imports for speed.

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

Schedule regular backups and monitor replication lag with automated scripts.

Miscellaneous Tips

Enable the query cache for repeated identical queries.

Use EXPLAIN to inspect execution plans and identify bottlenecks.

Apply LIMIT 1 when only a single row is needed to stop scanning early.

Write SQL statements in uppercase for readability; Oracle automatically upper‑cases keywords.

Avoid excessive whitespace in statements, as the query cache does not trim it.

By systematically applying these guidelines—optimizing indexes, rewriting queries, managing locks, choosing appropriate storage engines, and maintaining disciplined backup routines—developers can achieve significant performance gains in MySQL and other relational databases.