50 Essential SQL Performance Optimization Tips Every Developer Should Know

SQL Statement Performance Optimization

1. Optimize queries by avoiding full table scans; create indexes on columns used in WHERE and ORDER BY clauses.

2. Avoid NULL checks in WHERE; define columns as NOT NULL or use sentinel values such as 0 or -1.

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

4. Avoid OR in WHERE as it forces a full scan; use UNION instead, e.g.,

select id from t where num=10 union all select id from t where num=20

.

5. Prefer BETWEEN over IN for continuous numeric ranges, e.g., select id from t where num between 1 and 3.

6. Leading wildcard LIKE patterns ( like '%abc%' or like '%abc') cause full scans; use full‑text search. Prefix wildcard ( like 'abc%') can use an index.

7. Using parameters in WHERE can also lead to full scans.

8. Avoid expressions or functions on columns in WHERE clauses.

9. Prefer EXISTS over IN, e.g., replace select num from a where num in (select num from b) with

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

.

10. Indexes speed up SELECT but slow INSERT / UPDATE; keep the number of indexes per table reasonable (generally ≤6) and evaluate necessity per column.

11. Minimize updates to clustered index columns; if frequent, reconsider using a clustered index.

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

13. Prefer VARCHAR / NVARCHAR over CHAR / NCHAR for variable‑length strings to save space and improve query speed.

14. Avoid SELECT *; list only required columns.

15. Do not return excessively large result sets to the client; assess whether the data volume is necessary.

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

17. Store intermediate results in temporary tables to reduce repeated scans and lock contention, improving concurrency.

18. Apply NOLOCK where appropriate to improve read concurrency, but never on queries that modify data.

19. Limit joins to no more than five tables; use temporary tables or table variables for intermediate results and avoid deep view nesting.

20. Pre‑calculate frequently needed results and store them in tables for fast retrieval (e.g., hospital fee calculations).

21. Split OR conditions into separate queries combined with UNION; if indexes exist, UNION ALL can be more efficient.

22. In IN lists, place the most frequent values first to reduce comparison work.

23. Perform data processing on the server using stored procedures; for repeated dynamic SQL, use temporary stored procedures placed in tempdb.

24. When memory permits, set thread count to max_connections + 5; otherwise rely on the SQL Server thread pool.

25. Order tables in joins so the table with the fewest rows is processed first.

26. Use EXISTS instead of COUNT(1) to test for row existence.

27. Prefer >= over > when appropriate.

28. Index creation guidelines: limit total indexes, index selective and small columns, avoid indexing large text fields, carefully design composite indexes, and periodically rebuild or drop unused indexes.

29. Example of poorly performing queries and their optimized forms: SELECT * FROM record WHERE substrINg(card_no,1,4)='5378' (13 s) → SELECT * FROM record WHERE card_no LIKE '5378%' (<1 s) SELECT * FROM record WHERE amount/30 < 1000 (11 s) → SELECT * FROM record WHERE amount < 1000*30 (<1 s)

SELECT * FROM record WHERE convert(char(10),date,112)='19991201'

(10 s) → SELECT * FROM record WHERE date = '1999-12-01' (<1 s)

30. Use batch INSERT or batch UPDATE for bulk data modifications instead of row‑by‑row operations.

31. Avoid loops in stored procedures; use set‑based operations such as recursive queries when possible.

32. Choose the most efficient table order in joins (right‑to‑left processing in Oracle) and select the smallest driving table first.

33. Improve GROUP BY performance by filtering unnecessary rows before grouping.

34. Write SQL statements in uppercase; MySQL parses and normalizes case internally.

35. Use short aliases for tables and columns to speed up parsing and reduce ambiguity.

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

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

38. Avoid triggers when constraints can achieve the same logic; do not share trigger code across INSERT, UPDATE, DELETE.

39. Index creation rules: primary/foreign keys must be indexed; tables with >300 rows should have indexes; frequently joined tables need indexes on join columns; high‑selectivity columns are ideal for indexing.

40. MySQL query optimization summary: use slow‑query log, examine execution plans, avoid COUNT(*) on large tables, prefer GROUP BY over DISTINCT, and ensure indexed columns are used in WHERE, GROUP BY, and ORDER BY.

41. MySQL backup process: stop replication on the secondary server, shut down MySQL, copy data files, optionally use mysqldump with --opt, backup binary logs, and disable foreign‑key checks during import.

42. Reduce unnecessary spaces in SQL statements as the query buffer does not trim them automatically.

43. For sharding, use a hash of the username rather than an artificial mid column; let MySQL's partitioning handle the distribution.

44. Define an auto‑incrementing unsigned integer ID as the primary key for every table.

45. In stored procedures and triggers, set SET NOCOUNT ON at the start and SET NOCOUNT OFF at the end to suppress extra DONE_IN_PROC messages.

46. Enable MySQL query cache to speed up repeated identical queries.

47. Use EXPLAIN SELECT to analyze how MySQL executes a query and identify bottlenecks.

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

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

50. Optimize data types: use the smallest suitable type, avoid NULL when possible, prefer ENUM for fixed sets, and choose MEDIUMINT over BIGINT when appropriate.

51. Choose between CHAR, VARCHAR, and TEXT based on length variability and storage needs.

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