Comprehensive Guide to SQL Optimization Strategies and Best Practices

Introduction

Interviewers at major tech companies increasingly focus on SQL optimization experience, making it a critical skill for developers. Effective SQL tuning can dramatically improve system performance and reduce operational costs.

Table of Contents

SELECT statement – syntax order

SELECT statement – execution order

SQL optimization strategies

Avoid non‑index scenarios

Other SELECT optimizations

DML (INSERT/UPDATE/DELETE) optimizations

Query condition optimizations

Table creation optimizations

SQL Optimization Strategies

Optimization cost hierarchy: hardware > system configuration > table structure > SQL and indexes. Optimization effect hierarchy: hardware < system configuration < table structure < SQL and indexes.

String result = "嗯，不错，";
if ("SQL优化经验足") {
    if ("熟悉事务锁") {
        if ("并发场景处理666") {
            if ("会打王者荣耀") {
                result += "明天入职";
            }
        }
    }
} else {
    result += "先回去等消息吧";
}
Logger.info("面试官：" + result);

Below are five principles for MySQL‑level optimization:

Reduce data access: choose appropriate field types, enable compression, and use indexes to minimize disk I/O.

Return less data: select only needed columns and paginate results.

Reduce interaction count: batch DML operations and use stored functions.

Lower CPU overhead: avoid full‑table scans, sorting, and large memory usage.

Leverage more resources: use table partitioning for parallelism.

Avoid Non‑Index Scenarios

1. Leading wildcard searches cause full table scans. Use trailing wildcards instead.

SELECT * FROM t WHERE username LIKE '%陈%';  -- bad
SELECT * FROM t WHERE username LIKE '陈%';   -- good

If a leading wildcard is required, consider INSTR(), full‑text indexes, or external search engines like Elasticsearch.

2. IN / NOT IN can prevent index usage. Replace with BETWEEN for continuous ranges or EXISTS for subqueries.

SELECT * FROM t WHERE id IN (2,3);               -- bad
SELECT * FROM t WHERE id BETWEEN 2 AND 3;       -- good
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id); -- good

3. OR conditions also disable indexes; use UNION instead.

SELECT * FROM t WHERE id = 1 OR id = 3;   -- bad
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3;          -- good

4. NULL checks can lead to full scans; prefer default values.

SELECT * FROM t WHERE score IS NULL;   -- bad
SELECT * FROM t WHERE score = 0;      -- good

5. Functions on indexed columns (e.g., score/10 = 9) force scans; move expressions to the right side.

SELECT * FROM T WHERE score/10 = 9;   -- full scan
SELECT * FROM T WHERE score = 90;   -- uses index

6. Redundant WHERE 1=1 should be removed when building dynamic queries. SELECT username, age FROM T WHERE 1=1; -- bad 7. Avoid <> or != on indexed columns unless necessary, and reconsider index design.

8. Composite index misuse – the leftmost column must be present in the WHERE clause.

SELECT col1 FROM table WHERE key_part2=1 AND key_part3=2;  -- does not use index

9. Implicit type conversion prevents index usage.

SELECT col1 FROM table WHERE col_varchar = 123;  -- bad

10. ORDER BY without matching WHERE may cause extra sorting.

SELECT * FROM t ORDER BY age;                     -- no index
SELECT * FROM t WHERE age > 0 ORDER BY age;      -- uses index

When ORDER BY fields appear in the WHERE clause and are covered by an index, the database can return already sorted results, eliminating a separate sort step.

Using Index Hints

MySQL supports USE INDEX, IGNORE INDEX, and FORCE INDEX to influence optimizer choices, though they should be used sparingly.

SELECT col1 FROM table USE INDEX (mod_time, name);
SELECT col1 FROM table IGNORE INDEX (priority);
SELECT col1 FROM table FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

Other SELECT Optimizations

Avoid SELECT *; specify needed columns to enable index covering scans.

Avoid nondeterministic functions (e.g., NOW(), RAND()) in replication scenarios.

Place smaller tables first in multi‑table joins to reduce scan size.

Use table aliases to shorten parsing time and avoid ambiguity.

Prefer WHERE over HAVING for filtering before aggregation.

Order WHERE clause conditions by selectivity to reduce intermediate result sets.

DML Optimizations

Batch inserts using a single INSERT ... VALUES (...),(...) statement reduces parsing overhead and network round‑trips.

Commit periodically to release undo and redo logs and reduce lock contention.

Use variables to avoid a second SELECT after an UPDATE.

Adjust statement priority with LOW_PRIORITY, HIGH_PRIORITY, or DELAYED as appropriate.

Query Condition Optimizations

Use temporary tables for complex intermediate results.

Suppress sorting in GROUP BY when not needed by adding ORDER BY NULL.

Prefer joins over subqueries when indexes exist on join columns.

Use UNION ALL instead of UNION when duplicate elimination is unnecessary.

Split large transactions into smaller statements to improve concurrency and enable query cache.

Prefer TRUNCATE over DELETE for full‑table removal to avoid logging and undo generation.

Adopt efficient pagination strategies, such as using covering indexes or sub‑queries that fetch primary keys first.

Table Design Optimizations

Create indexes on columns frequently used in WHERE or ORDER BY.

Prefer numeric types over character types for enumerated data to reduce storage and improve comparison speed.

For very large tables, paginate using row numbers or window functions.

Use VARCHAR / NVARCHAR instead of fixed‑length CHAR / NCHAR to save space and improve query speed.

Remember that NULL still consumes space in fixed‑length columns.

The article concludes that mastering these SQL optimization techniques can significantly boost database performance, lower hardware costs, and give developers a decisive edge in technical interviews.