Mastering SQL Execution Order and Optimization: Practical Tips and Best Practices

SQL Execution Order

FROM <left_table>

ON <join_condition>

JOIN <right_table> (specify join type, e.g., INNER, LEFT, RIGHT)

WHERE <filter_condition>

GROUP BY <group_columns>

Aggregate Functions AGG_FUNC(column|expression), …

HAVING <having_condition>

SELECT …

DISTINCT column, …

ORDER BY <order_columns>

LIMIT count OFFSET count

Basic SQL Optimization

Avoid SELECT * SELECT id, NAME FROM student; Transfers only required columns, reducing network I/O.

Enables the optimizer to use indexes; SELECT * often forces a full table scan.

Do not use OR in WHERE clauses

SELECT * FROM student WHERE id = 1 OR salary = 30000;

Rewrite using UNION ALL or separate queries so each condition can use an index:

SELECT * FROM student WHERE id = 1
UNION ALL
SELECT * FROM student WHERE salary = 30000;

Prefer VARCHAR over CHAR for variable‑length text

`deptname` VARCHAR(100) DEFAULT NULL COMMENT '部门名称';

VARCHAR

stores only the actual length, saving space. CHAR pads with spaces, wasting storage and slowing scans.

Use numeric types for identifiers and status fields

Primary key: INT or TINYINT Gender: TINYINT (0 = female, 1 = male)

Payment method, service state, product state: store as small integers with documented enum values.

Limit result set size Paginate large queries (e.g., 10/20/50/100 rows per page) to reduce query time and network transfer.

Analyze execution plans with EXPLAIN EXPLAIN SELECT * FROM student WHERE id = 1; The type column shows the access method. Common values:

ALL – full table scan (slowest)

index – index scan

range – index range scan (e.g., >=, BETWEEN)

ref – non‑unique index lookup

eq_ref – unique index lookup (primary key)

const – constant row (primary key equality)

system – single‑row table

Performance ranking: system > const > eq_ref > ref > range > index > ALL .

Create indexes on frequently filtered or sorted columns ALTER TABLE student ADD INDEX idx_name (NAME); Indexes dramatically speed up lookups and ORDER BY operations.

Optimize LIKE patterns

EXPLAIN SELECT id, NAME FROM student WHERE NAME LIKE '1%';

Leading wildcards (e.g., '%1' ) prevent index usage; place the literal prefix first.

Quote string literals correctly

# Without quotes – index disabled
SELECT * FROM student WHERE NAME = 123;

# With quotes – index used
SELECT * FROM student WHERE NAME = '123';

Mismatched types cause implicit conversion and a full scan.

Keep the number of indexes low (≤5 per table)

Too many indexes increase insert/update overhead and storage.

Each index stores a sorted copy of column data.

Rebuilding indexes on large tables is costly.

Avoid indexing columns with high duplicate values Columns such as gender often have low selectivity; the optimizer may skip the index.

Filter data before grouping

-- Good: filter then group
SELECT job, AVG(salary) FROM employee WHERE job IN ('president','manager') GROUP BY job;

Use composite (multi‑column) indexes with leftmost prefix

ALTER TABLE student ADD INDEX idx_name_salary (NAME, salary);

Queries that filter on NAME alone can use the leftmost part of the composite index.

EXPLAIN SELECT * FROM student WHERE NAME = 'name1';

Index columns used in ORDER BY

EXPLAIN SELECT id, NAME FROM student ORDER BY NAME;

Using * without an index on the ordered column forces a full scan.

Remove redundant or duplicate indexes

SHOW INDEX FROM student;

DROP INDEX idx_name ON student;

Limit the number of joined tables (≤5)

More tables increase compilation time and memory usage.

Each join creates a temporary table.

If many tables are required, consider redesigning the schema.

Prefer INNER JOIN over LEFT/RIGHT JOIN when results are equivalent INNER JOIN returns only matching rows and is usually faster.

If a left join is needed, keep the left table as small as possible.

Rewrite IN subqueries as joins

# Using IN (may be slower)
SELECT * FROM tb_user WHERE dept_id IN (SELECT id FROM tb_dept);

# Equivalent join (often faster)
SELECT u.* FROM tb_user u JOIN tb_dept d ON u.dept_id = d.id;

Prefer UNION ALL over UNION

# UNION removes duplicates (extra sort)
SELECT * FROM student UNION SELECT * FROM student;

# UNION ALL simply concatenates results
SELECT * FROM student UNION ALL SELECT * FROM student;

UNION ALL is faster when duplicate removal is not required.

Advanced SQL Optimization

Batch inserts

INSERT INTO student (id, NAME) VALUES (4, 'name1'), (5, 'name2');

Multiple rows in a single statement reduce transaction overhead.

Batch deletes (partitioned)

# Delete in small chunks
DELETE FROM student WHERE id < 500;

# Delete a range
DELETE FROM student WHERE id >= 500 AND id < 1000;

Large deletions can cause table locks and timeout errors.

Soft delete (pseudo‑delete) Mark rows with a status flag (e.g., state = 0 ) instead of physically removing them. This preserves history and avoids cascade issues.

Filter before GROUP BY Apply WHERE conditions first to reduce the number of rows that need to be grouped.

Composite index left‑most rule Creating an index on (k1, k2, k3) also creates usable prefixes (k1) and (k1, k2) . Queries must reference the leftmost columns to benefit.

Index columns used in ORDER BY Sorting on columns without an index forces a full scan.

Remove redundant indexes Use SHOW INDEX and drop duplicates to keep the index set lean.

Avoid functions on indexed columns

# Bad: function on indexed column
SELECT * FROM student WHERE DATE_ADD(birthday, INTERVAL 7 DAY) >= NOW();

# Good: use column directly
SELECT * FROM student WHERE birthday >= DATE_ADD(NOW(), INTERVAL 7 DAY);

Functions prevent index usage, leading to full scans.

Avoid != or &lt;&gt; operators

# Bad
SELECT * FROM student WHERE salary != 3000;

# Bad
SELECT * FROM student WHERE salary <> 3000;

These operators often cause the optimizer to skip indexes.

Limit columns in DISTINCT

# High CPU cost
SELECT DISTINCT * FROM student;

# Better
SELECT DISTINCT id, NAME FROM student;

Fewer columns reduce comparison work and improve performance.

Prefer default values over NULL in WHERE

# Less index-friendly
SELECT * FROM student WHERE age IS NOT NULL;

# More index-friendly
SELECT * FROM student WHERE age > 0;

Concrete numeric comparisons are more likely to use indexes.