5 Essential Rules to Write High‑Performance SQL Queries

Rule 1: Return Only the Needed Results

Always specify a WHERE clause to filter rows; this enables the optimizer to use indexes instead of performing a full‑table scan. Indexes (B‑tree, B+‑tree, etc.) provide logarithmic search time, dramatically reducing I/O. Avoid SELECT * because it forces the engine to read all columns, increasing data transfer and slowing the query.

Rule 2: Ensure the Query Uses the Correct Index

Create indexes on columns that frequently appear in WHERE, are used for ORDER BY, serve as join keys, or are grouped. Common index‑loss pitfalls include:

Applying functions or expressions to indexed columns in WHERE (also mismatched data types).

Using a left‑anchored LIKE pattern that prevents index usage.

Leaving indexed columns nullable when the DB cannot leverage the index for IS NULL checks.

Inspect the execution plan with EXPLAIN to verify that the intended index is chosen.

Reference articles: https://tonydong.blog.csdn.net/article/details/103579177 https://blog.csdn.net/horses/article/details/106905110

Rule 3: Avoid Subqueries When Possible

Subqueries often cause repeated scans. In MySQL, the following query finds employees earning more than 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 work:

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 subquery result once, then joins, yielding a faster execution. Oracle and SQL Server perform this unnesting automatically; PostgreSQL switches from a nested‑loop to a hash join after the rewrite.

Rule 4: Don't Use OFFSET for Pagination

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

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

A more efficient approach remembers the last retrieved id and uses it as a filter:

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

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

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

Rule 5: Understand the Logical Execution Order of SQL Clauses

The logical order differs from the written order. The sequence is:

FROM and JOIN – produce the Cartesian product.

ON – filter the join result.

WHERE – further filter rows.

GROUP BY – group rows and compute aggregates.

HAVING – filter groups.

SELECT – choose output columns (apply DISTINCT if needed).

UNION/INTERSECT/EXCEPT – combine result sets.

ORDER BY – sort the final rows.

OFFSET/FETCH (or LIMIT/TOP) – restrict the number of rows returned.

Knowing this order helps place filters early (WHERE before HAVING) and avoid errors such as referencing column aliases in WHERE:

-- Incorrect example
SELECT emp_name AS empname
FROM employee
WHERE empname = '张飞';

Similarly, after GROUP BY only grouped columns and aggregates are available; selecting non‑grouped columns causes errors:

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

Understanding these rules prevents logical mistakes and guides effective optimization.

Conclusion

SQL optimization fundamentally means understanding how the optimizer works, creating appropriate indexes, writing queries that allow the optimizer to choose efficient plans, and, when necessary, manually guiding it to achieve better performance.