5 Essential Rules for Writing High‑Performance SQL Queries

SQL is easy to learn, but writing queries that run efficiently requires understanding how the optimizer works and applying proven best‑practice rules.

Rule 1: Return Only the Needed Results

Always add appropriate WHERE conditions to filter rows early. In OLTP workloads this lets the database use indexes to avoid full‑table scans, reducing I/O from millions of rows to just a few index lookups.

For example, a clustered B‑tree index on a million rows needs only three index levels, resulting in three index reads plus one data read, whereas a full scan would require orders of magnitude more I/O.

Rule 2: Ensure the Query Uses the Correct Index

Create indexes on columns that appear frequently in WHERE, ORDER BY, join conditions, or GROUP BY. Common candidates include:

Columns used in WHERE clauses.

Columns used for sorting ( ORDER BY).

Foreign‑key columns in join predicates.

Columns used in GROUP BY aggregations.

Even with an index, a poorly written query can prevent its use. Issues that invalidate indexes include applying functions or expressions to indexed columns, mismatched data types, left‑anchored LIKE patterns, and nullable indexed columns that are not declared NOT NULL.

Rule 3: Avoid Subqueries When Possible

Subqueries often cause repeated scans. The following MySQL example finds employees earning more than the average salary of their department:

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

The execution 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 department averages once, then joins, resulting in a faster plan (hash join in PostgreSQL, nested‑loop with materialization in MySQL).

Rule 4: Don’t Use OFFSET for Pagination

Using OFFSET N forces the engine to read and discard the first N rows, which becomes slower as N grows. Instead, remember the last retrieved key and query the next page with a WHERE id > last_id predicate:

-- Bad pagination (slow with large OFFSET)
SELECT * FROM large_table ORDER BY id LIMIT 10 OFFSET 1000000;

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

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

Rule 5: Know the Logical Execution Order of SQL Clauses

The logical order (not the written order) is crucial for optimization:

FROM / JOIN : Build the Cartesian product.

ON : Apply join predicates.

WHERE : Filter rows before grouping.

GROUP BY : Form groups and compute aggregates.

HAVING : Filter groups.

SELECT : Choose columns, apply DISTINCT, compute expressions.

UNION / INTERSECT / EXCEPT : Combine result sets.

ORDER BY : Sort the final rows.

OFFSET / FETCH / LIMIT : Limit the output.

Understanding this order helps you place filters in the most efficient clause (e.g., using WHERE instead of HAVING when possible) and avoid logical errors such as referencing column aliases in WHERE.

Conclusion

SQL performance tuning is essentially about understanding the optimizer’s behavior, creating appropriate indexes, and writing queries that guide the optimizer toward the most efficient plan. When the optimizer falls short, manual rewrites—such as replacing subqueries with joins or using keyset pagination—can dramatically improve execution speed.