52 SQL Query Performance Optimization Strategies

This article provides 52 detailed SQL performance optimization strategies aimed at reducing full table scans, improving index utilization, and enhancing overall query efficiency.

Index and Query Design : Create indexes on columns used in WHERE and ORDER BY, avoid functions or expressions on indexed columns, limit the use of OR by rewriting with UNION, prefer IN with ordered values, and replace IN with EXISTS when appropriate.

Avoiding Inefficient Patterns : Do not use != or <>, avoid NULL checks in WHERE, refrain from using LIKE '%abc%' (use full‑text search or prefix LIKE 'abc%'), and limit the number of joined tables (prefer temporary tables for intermediate results).

Use of Temporary Structures : Employ temporary tables or table variables instead of excessive temporary tables, and consider NOLOCK only for read‑only queries where dirty reads are acceptable.

Index Maintenance : Keep the number of indexes per table reasonable (ideally ≤6), rebuild indexes periodically, and remove unused indexes to avoid overhead on INSERT/UPDATE operations.

Data Types and Schema Design : Prefer numeric over character fields, use VARCHAR instead of CHAR for variable‑length data, choose appropriate integer sizes (e.g., MEDIUMINT vs INT), and define primary keys as INT UNSIGNED AUTO_INCREMENT. Use ENUM for limited categorical values.

Query Execution Tips : Use EXPLAIN to analyze execution plans, apply USE INDEX when the optimizer picks a suboptimal index, limit result sets with LIMIT 1 when only one row is needed, and avoid SELECT * in favor of explicit column lists.

Transaction and Lock Management : Keep transactions short, acquire locks in a consistent order, and avoid triggers when constraints can achieve the same logic.

Backup and Maintenance : Perform backups from replica servers, stop replication during backup, use mysqldump --opt, disable foreign key checks and unique checks during bulk imports, and monitor replication lag.

Storage Engine Choice : Choose MyISAM for read‑heavy workloads with few writes, and InnoDB for transactional workloads requiring consistency and concurrency.

Sample Code :

select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681'

SELECT * FROM record WHERE substrINg(card_no,1,4) = '5378' -- 13 seconds

SELECT * FROM record WHERE card_no like '5378%' -- < 1 second

By following these guidelines, developers can achieve faster query response times, lower resource consumption, and more maintainable database schemas.