5 Essential Rules for Writing High‑Performance SQL Queries

Rule 1: Return Only the Needed Results

Always specify WHERE conditions to filter rows and avoid SELECT *. Using indexes (B‑tree, B+‑tree) enables logarithmic‑time lookups instead of full‑table scans, dramatically reducing I/O. For example, an index with 100 records per node can locate a row in three reads for a million‑row table, whereas a full scan would require many more reads.

Rule 2: Use the Correct Indexes

Create indexes on columns that appear frequently in WHERE, on columns used for ORDER BY, on join keys, and on GROUP BY fields. Common pitfalls that invalidate indexes include applying functions or expressions to indexed columns, mismatched data types, using leading wildcards with LIKE, and not declaring indexed columns as NOT NULL when required.

Reference: 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);

Rewriting it as a join eliminates the repeated subquery 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 join version materializes the subquery result once, then joins, resulting in faster execution across MySQL, Oracle, SQL Server, and PostgreSQL.

Rule 4: Don’t Use OFFSET for Pagination

OFFSET forces the database to scan and discard rows before returning the desired page, which becomes slower as the offset grows. Instead, remember the last retrieved id and query for rows greater than that value:

-- Bad pagination with OFFSET
SELECT * FROM large_table
ORDER BY id
LIMIT 10 OFFSET N;

-- Better keyset pagination
SELECT * FROM large_table
WHERE id > last_id
ORDER BY id
LIMIT 10;

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

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

Rule 5: Understand SQL’s Logical Execution Order

The logical order differs from the written order. It proceeds as:

FROM and JOIN (produce the Cartesian product).

ON (filter join results).

WHERE (additional filtering).

GROUP BY (grouping and aggregation).

HAVING (filter groups).

SELECT (choose columns, apply DISTINCT, aliases).

UNION/INTERSECT/EXCEPT (combine result sets).

ORDER BY (sort the final set).

OFFSET/FETCH (limit rows returned).

Knowing this order helps avoid mistakes such as referencing column aliases in WHERE or selecting non‑grouped columns after a GROUP BY. For example, the following query incorrectly uses a column alias in the WHERE clause:

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

Since WHERE is evaluated before SELECT, the alias does not exist yet.

Similarly, after GROUP BY, only grouped columns and aggregates are available; attempting to select other columns leads to errors unless window functions are used.

Conclusion

SQL optimization fundamentally means understanding how the optimizer works, creating appropriate indexes, and writing queries that guide the optimizer toward efficient execution paths. When the optimizer falls short, manual rewrites—such as adding indexes, eliminating subqueries, or using keyset pagination—can make the query run much faster.