52 SQL Query Performance Optimization Strategies

This article provides 52 detailed SQL performance optimization strategies aimed at improving query efficiency and overall database performance.

Indexing and query design: Create indexes on columns used in WHERE and ORDER BY, avoid full table scans, limit the number of indexes (preferably no more than six per large OLTP table), and use selective, small, numeric fields for indexing.

Avoid using NULL checks in WHERE clauses; prefer NOT NULL or sentinel values like 0 or -1.

Do not use != or <> operators; MySQL indexes support <, <=, =, >, >=, BETWEEN, IN, and some LIKE patterns.

Avoid OR in WHERE clauses as it can disable index usage; rewrite using UNION when appropriate:

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 patterns like '%abc%' and index‑friendly patterns like 'abc%': select id from t where name like 'abc%' Replace IN with EXISTS when possible:

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

Limit the number of joins (prefer fewer than five) and consider temporary tables or table variables for intermediate results.

Use EXISTS instead of COUNT(1) to test for row existence, as COUNT scans all rows.

Prefer >= over > where appropriate, and follow index usage guidelines such as forcing indexes with USE INDEX when the optimizer picks the wrong one.

When using GROUP BY, filter rows before grouping for better performance:

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

Use uppercase for SQL keywords for readability and consistency.

Alias tables and columns to reduce parsing time and avoid ambiguous column references.

Avoid unnecessary temporary tables; use table variables when possible, as they reside in memory and are faster.

Minimize trigger usage; prefer constraints when feasible, and avoid using the same trigger for multiple events.

Follow index creation rules: primary/foreign keys must be indexed, tables with >300 rows should have indexes, and frequently joined columns need indexes.

Keep indexes simple, avoid redundant indexes, and regularly rebuild or reorganize them.

Choose appropriate storage engines: MyISAM for read‑heavy workloads, InnoDB for transactional workloads.

Optimize data types: use the smallest suitable integer type, prefer ENUM for fixed sets, avoid large CHAR fields, and set sensible defaults to avoid NULL.

Enable query cache for repeated queries and use EXPLAIN to analyze execution plans.

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

Backup best practices include using secondary replicas, stopping replication during backup, using mysqldump --opt, disabling foreign key checks during import, and monitoring backup sizes.

Overall, the article emphasizes careful schema design, judicious use of indexes, query rewriting, and proper MySQL configuration to achieve optimal performance.