52 Proven SQL Performance Tweaks to Supercharge Your Queries

Source: SimpleWu, cnblogs.com/SimpleWu/p/9929043.html

Prefer indexes on columns used in WHERE and ORDER BY to avoid full table scans.

Define columns as NOT NULL (or use sentinel values like 0 or -1) instead of allowing NULL checks in WHERE.

Avoid the != or <> operators; MySQL indexes only <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE.

Replace OR conditions with UNION to keep 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 numeric ranges, e.g., select id from t where num between 1 and 3.

Wildcard patterns that start with % cause full scans; use leading characters (e.g., name like 'abc%') or full‑text search for %abc patterns.

Parameterised predicates in WHERE can also trigger scans; ensure they match indexed columns.

Avoid expressions or functions on indexed columns in WHERE clauses.

Prefer EXISTS over IN for sub‑queries, e.g.,

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

.

Indexes speed up SELECT but add overhead to INSERT / UPDATE; keep the number of indexes per table under six and only on frequently queried columns.

Minimise updates to clustered index columns because they reorder physical storage and are costly.

Use numeric data types where possible; avoid storing pure numbers as character strings.

Prefer VARCHAR / NVARCHAR over fixed‑length CHAR / NCHAR for variable‑size data.

Never use SELECT *; list only required columns.

Limit result set size sent to clients; large result sets should be paginated or filtered.

Alias tables in joins to reduce parsing time and avoid column ambiguity.

Use temporary tables to store intermediate results, reducing repeated scans and lock contention.

Apply NOLOCK only for read‑only queries where dirty reads are acceptable; follow three principles: not for DML, avoid on heavily fragmented tables, and prefer temporary tables over NOLOCK when possible.

Keep the number of joined tables below five; use temporary tables or table variables for intermediate data.

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

Rewrite multiple OR conditions as separate queries combined with UNION ALL when indexes can be used.

Order values in IN lists by frequency, most common first.

Push as much processing as possible to the server (e.g., stored procedures) to reduce network overhead.

Set AUTO_INCREMENT primary keys as unsigned INT for efficiency.

In stored procedures and triggers, set SET NOCOUNT ON to suppress unnecessary row‑count messages.

Enable MySQL query cache for repeated identical queries to improve response time.

Use EXPLAIN to inspect execution plans and identify bottlenecks.

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

Choose the appropriate storage engine: MyISAM for read‑heavy workloads, InnoDB for transactional consistency.

For InnoDB, wrap multiple statements in an explicit transaction (disable autocommit) to avoid per‑statement commits.

Keep table definitions narrow: use the smallest suitable integer type, avoid unnecessary CHAR lengths, and prefer ENUM for fixed sets like gender or province.

Prefer MEDIUMINT over BIGINT when the range permits; set columns to NOT NULL to avoid NULL checks.

When using LIKE, avoid leading wildcards; use full‑text indexes for pattern searches.

Limit the number of indexes per table; prioritize high‑selectivity columns and those frequently used in WHERE, JOIN, GROUP BY, or ORDER BY.

Do not index large text columns; index only the most selective, small columns.

For composite indexes, ensure the leftmost column is always used in queries; otherwise the index will be ignored.

If a composite index’s columns are often queried individually, consider separate single‑column indexes instead.

Avoid over‑indexing tables that experience heavy DML; excess indexes increase write latency.

Periodically rebuild indexes and recompile stored procedures to keep statistics fresh.

Remove unused indexes to prevent optimizer mis‑choices.

Do not index columns with low cardinality or many duplicate values.

Use the slow‑query log and EXPLAIN to identify and tune problematic statements.

Avoid COUNT(*) on large tables; use indexed columns or approximate counts when possible.

Prefer GROUP BY over DISTINCT when appropriate, and filter rows before grouping.

When an index is not used, force it with USE INDEX or rewrite the query.

For bulk inserts/updates, use batch operations instead of row‑by‑row statements.

Avoid loops in stored procedures; use set‑based operations instead.

When partitioning, let MySQL handle it; avoid manual hash‑mod logic in application code.

Set thread count to max_connections + 5 if memory permits; otherwise keep it below max_connections.

Order tables in joins so that the table with the smallest result set appears first (Oracle parses FROM right‑to‑left).

For GROUP BY, filter unnecessary rows before grouping to improve speed.

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

Use short table/column aliases to reduce parsing overhead.

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.

Minimise trigger usage; use constraints when they can replace trigger logic.

When using triggers, avoid shared code across different DML events and do not embed transaction control inside them.

Ensure primary and foreign key columns are indexed; index columns with >300 rows and those frequently used in joins or WHERE.

Do not index columns with many duplicate values; focus on high‑selectivity fields.

Backup MySQL using replica servers, stop replication during backup, and use mysqldump --opt with binary logs.

Disable foreign‑key checks and unique checks during bulk imports to speed up loading.

Monitor backup size and schedule regular backups; automate replication health checks.

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

For sharding, hash on the user‑visible identifier (e.g., username) rather than an internal ID.

Use ENUM for fixed small sets (e.g., gender, province) to improve storage and comparison speed.

Choose appropriate string types: CHAR for fixed‑length, VARCHAR for variable‑length, and avoid overly large definitions.

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