52 Proven SQL Performance Optimization Tips to Supercharge Your Queries

1. Indexing and Query Planning

Avoid full table scans; create indexes on columns used in WHERE and ORDER BY clauses.

Prefer NOT NULL columns or sentinel values (e.g., 0, -1) instead of nullable columns.

Use index‑friendly operators only: <, <=, =, >, >=, BETWEEN, IN, and, when appropriate, LIKE with a left‑anchored pattern.

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 continuous ranges, e.g.: SELECT id FROM t WHERE num BETWEEN 1 AND 3; Use prefix LIKE patterns ( abc%) to leverage indexes; avoid leading wildcards ( %abc or %abc%) which force scans.

Do not apply functions or expressions to indexed columns in WHERE clauses.

Prefer EXISTS over IN for existence checks.

Limit the number of indexes per table (ideally ≤ 6) and avoid indexing rarely queried columns.

Avoid frequent 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.

Limit result‑set size sent to clients; evaluate whether large transfers are necessary.

2. Query Simplification Techniques

Use short table aliases to reduce parsing overhead.

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

Apply NOLOCK only for read‑only queries that can tolerate dirty reads; never on tables involved in writes.

Keep the number of joined tables ≤ 5; for more complex joins, materialize intermediate results first.

Pre‑compute frequently needed results and store them in lookup tables.

Rewrite multiple OR conditions as separate queries combined with UNION ALL to enable index usage.

Order values in an IN list by frequency (most common first) to reduce comparisons.

Push filtering predicates before GROUP BY to shrink the data set.

Prefer filtering before aggregation, e.g.:

SELECT job, AVG(sal) FROM emp WHERE job IN ('PRESIDENT','MANAGER') GROUP BY job;

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

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

Prefer table variables over temporary tables when the data fits in memory.

Avoid triggers when constraints can enforce the same logic; if triggers are required, separate them by operation (INSERT, UPDATE, DELETE) and avoid transactional code inside.

3. Index Design Rules

Primary keys and foreign keys must be indexed.

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

Index columns used in joins, WHERE, GROUP BY, and ORDER BY clauses.

Choose high‑selectivity columns; keep index key size small.

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

Periodically rebuild indexes and recompile stored procedures.

Delete unused indexes to prevent plan bloat.

Do not index large text or BLOB columns.

Prefer single‑column indexes unless the column set is always queried together with AND.

Limit composite indexes to ≤ 3 columns; otherwise reconsider their necessity.

Avoid excessive indexing on tables with heavy DML, as each index adds storage and write overhead.

4. Storage Engine & Data‑Type Choices (MySQL)

MyISAM : suited for read‑heavy workloads with few writes and low transaction requirements.

InnoDB : supports transactions and row‑level locking; batch inserts by disabling autocommit or wrapping statements in explicit BEGIN … COMMIT blocks.

Choose the smallest appropriate numeric type (e.g., MEDIUMINT instead of INT).

Prefer TIMESTAMP (4 bytes) over DATETIME (8 bytes) when the range 1970‑2038 is sufficient.

Define fixed‑length fields narrowly; use CHAR(6) or VARCHAR for short codes instead of CHAR(255).

Use ENUM for categorical fields (e.g., gender, province) to store them as numeric values.

Set NOT NULL with sensible defaults to avoid null‑related scans.

5. Diagnostic & Maintenance Tools

Enable the slow‑query log and examine EXPLAIN output to locate bottlenecks.

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

Leverage the query cache for repetitive reads, but be aware of its limitations.

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

Backup using replica servers or mysqldump --opt together with binary‑log backup.

Run OPTIMIZE TABLE before each backup and record table/index sizes for monitoring.

Monitor replication lag and errors with automated scripts.

6. Miscellaneous Performance Tips

Avoid leading/trailing spaces in SQL statements; they are not trimmed by the query buffer.

When partitioning, use MySQL's native PARTITION feature instead of manual sharding.

Define an INT UNSIGNED AUTO_INCREMENT primary key for every table.

Set SET NOCOUNT ON at the start of stored procedures and triggers to reduce network chatter.

Use INSERT … ON DUPLICATE KEY or INSERT IGNORE instead of separate SELECT ‑then‑ UPDATE cycles.

Prefer EXISTS over COUNT(1) for existence checks; COUNT(*) should be used only for full row counts.

Use >= rather than > when the boundary value is inclusive.

When joining many tables, place the table with the smallest row count (or the most selective driving table) first in the FROM clause.

Filter rows before GROUP BY to improve performance; see the example in section 2.

Write SQL keywords in uppercase for readability; Oracle parses statements case‑insensitively.

Use short aliases for tables and columns to improve parsing speed.

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

Prefer table variables over temporary tables when possible; they reside in memory and are faster.

Avoid triggers unless necessary; if used, separate them by operation and do not embed transactional logic.

7. Backup Best Practices

Perform backups from a replica to avoid impacting the primary.

Stop replication during backup to ensure consistency.

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

Avoid relying on LVM snapshots alone; they may produce inconsistent data.

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

Before backup, run OPTIMIZE TABLE and verify table statistics.

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

After each backup, record database, table, and index sizes to monitor growth.

Automate replication lag and error monitoring; schedule regular backups.

8. Common Pitfalls that Force Table Scans

Applying functions or expressions to indexed columns (e.g., SUBSTRING(col,1,4), CONVERT(col, CHAR), arithmetic expressions) prevents index usage.

Using leading wildcards in LIKE patterns ( %abc, %abc%).

Using != or <> operators, which MySQL does not index.

Placing OR conditions without rewriting to UNION ALL.

Storing numeric data in character columns.

9. Example Rewrites to Avoid Scans

Original (slow): SELECT * FROM record WHERE SUBSTRING(card_no,1,4)='5378'; Rewrite: SELECT * FROM record WHERE card_no LIKE '5378%'; Original (slow): SELECT * FROM record WHERE amount/30 < 1000; Rewrite: SELECT * FROM record WHERE amount < 1000*30; Original (slow):

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

Rewrite:

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

10. Summary

By applying these indexing strategies, query rewrites, appropriate storage‑engine choices, diligent diagnostics, and disciplined backup procedures, developers can substantially improve MySQL query response times, reduce server load, and maintain a healthier database environment.