5 Essential Rules for Writing High‑Performance SQL Queries

Rule 1: Return Only Needed Results

Always specify a WHERE clause to filter rows and avoid SELECT * . Using indexes (B‑tree) enables binary search with logarithmic complexity instead of costly full‑table scans.

Rule 2: Ensure Correct Index Usage

Create indexes on columns that frequently appear in WHERE , ORDER BY , JOIN , and GROUP BY . Avoid applying functions or expressions to indexed columns, mismatched data types, or left‑side LIKE patterns, and mark indexed columns as NOT NULL when possible. Use EXPLAIN to verify that the optimizer chooses the intended index.

Rule 3: Avoid Subqueries

Rewrite subqueries as JOIN operations. The example below finds employees whose salary exceeds the average salary of their department. The subquery version triggers repeated index lookups, while the JOIN version materializes the department averages once and is significantly faster.

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

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;

Rule 4: Do Not Use OFFSET for Pagination

Using OFFSET forces the database to scan and discard rows, causing performance to degrade as the offset grows. Instead, remember the last retrieved primary‑key value and query with a range condition.

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

Rule 5: Understand SQL Logical Execution Order

The logical processing sequence of a SQL statement is:

FROM and JOIN

ON

WHERE

GROUP BY

HAVING

SELECT (including DISTINCT and column aliases)

UNION / INTERSECT / EXCEPT

ORDER BY

OFFSET / FETCH (or LIMIT/TOP)

Knowing this order helps place filters early, avoid errors such as referencing a column alias in WHERE , and understand why certain predicates are evaluated before others.

Conclusion

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

References: https://tonydong.blog.csdn.net/article/details/104020721, https://tonydong.blog.csdn.net/article/details/103579177, https://tonydong.blog.csdn.net/article/details/108729112