5 Essential Rules to Write High‑Performance SQL Queries

Rule 1: Return Only the Needed Results

Always add appropriate WHERE conditions to filter rows; this enables the optimizer to use indexes (B‑tree, B+‑tree, etc.) instead of performing full table scans. Selecting specific columns instead of SELECT * also reduces I/O and network traffic.

For example, a B‑tree with 100 records per node can locate a row in three node reads for a million‑row table, whereas a full scan would require many more disk I/Os.

Rule 2: Ensure the Query Uses the Correct Index

Create indexes on columns that appear frequently in WHERE, on columns used for ORDER BY, on join keys, and on GROUP BY fields. Even with indexes, certain query patterns can invalidate them.

Index columns that are often used in WHERE conditions.

Add ORDER BY columns to the index to avoid extra sorting.

Index foreign‑key columns used in joins.

Include GROUP BY columns in the index to enable index‑only grouping.

Common reasons for index loss include applying functions or expressions to indexed columns, mismatched data types, using a leading wildcard in LIKE, or not declaring indexed columns as NOT NULL when required.

Inspect the execution plan (e.g., EXPLAIN) to verify that the optimizer chooses the intended index.

https://tonydong.blog.csdn.net/article/details/104020721

Rule 3: Avoid Subqueries When Possible

Subqueries often cause repeated scans. In MySQL, the following query uses a correlated subquery to find employees earning above the department average:

EXPLAIN ANALYZE
SELECT emp_id, emp_name
FROM employee e
WHERE salary > (
    SELECT AVG(salary)
    FROM employee
    WHERE dept_id = e.dept_id);

The plan shows a nested‑loop join with the subquery executed 25 times. Rewriting it as a join eliminates the repeated execution:

EXPLAIN ANALYZE
SELECT e.emp_id, e.emp_name
FROM employee e
JOIN (
    SELECT dept_id, AVG(salary) AS dept_average
    FROM employee
    GROUP BY dept_id
) t ON e.dept_id = t.dept_id
WHERE e.salary > t.dept_average;

The rewritten query materializes the department averages once, then joins, resulting in a faster execution. Similar transformations apply to IN and EXISTS subqueries.

Rule 4: Don't Use OFFSET for Pagination

Using OFFSET forces the database to scan and discard rows up to the offset, which becomes slower as the offset grows.

Typical MySQL pagination:

SELECT *
FROM large_table
ORDER BY id
LIMIT 10 OFFSET N;

A more efficient key‑set pagination remembers the last retrieved id and uses it as a filter:

SELECT *
FROM large_table
WHERE id > last_id
ORDER BY id
LIMIT 10;

When id is indexed, this method scales regardless of table size.

https://tonydong.blog.csdn.net/article/details/108729112

Rule 5: Understand the Logical Execution Order of SQL Clauses

The textual order of clauses differs from the logical execution order. The logical sequence is:

(1) FROM / JOIN
(2) ON
(3) WHERE
(4) GROUP BY
(5) HAVING
(6) SELECT
(7) UNION / INTERSECT / EXCEPT
(8) ORDER BY
(9) OFFSET / FETCH / LIMIT

FROM and JOIN produce a Cartesian product.

ON filters the join result.

WHERE further filters rows.

GROUP BY groups rows and computes aggregates.

HAVING filters groups.

SELECT chooses output columns (and applies DISTINCT if present).

Set operators combine result sets.

ORDER BY sorts the final rows.

OFFSET/FETCH (or LIMIT/TOP) limits the row count returned.

Knowing this order helps place filters in the most efficient clause. For example, referencing a column alias in WHERE is invalid because the alias is created later in the SELECT step.

-- Error example
SELECT emp_name AS empname
FROM employee
WHERE empname = 'Zhang Fei';

Similarly, after a GROUP BY, only grouped columns and aggregates are available; selecting non‑grouped columns leads to errors.

-- GROUP BY error example
SELECT dept_id, emp_name, AVG(salary)
FROM employee
GROUP BY dept_id;

If GROUP BY is used, subsequent SELECT, ORDER BY, etc., may reference only grouped fields or aggregate results.

Understanding these concepts enables better query design and avoids subtle bugs that degrade performance.

Conclusion

SQL optimization is fundamentally about understanding how the optimizer works, creating appropriate indexes, and writing queries that guide the optimizer toward efficient execution plans; when the optimizer falls short, manual rewrites can make it behave intelligently.