5 Essential Rules for Writing High‑Performance SQL Queries

Why SQL Optimization Matters

SQL is the standard language for relational databases. Efficient queries require proper use of indexes, understanding execution plans, and the logical order of SQL clauses. The following guidelines apply to MySQL, Oracle, SQL Server, PostgreSQL, SQLite and other RDBMS.

1. Return Only Required Columns

Always include a WHERE clause to filter rows and avoid SELECT *. Indexes (B‑tree, B+‑tree) provide logarithmic‑time lookups, preventing full‑table scans.

2. Ensure the Query Uses the Correct Index

Create indexes on columns that appear frequently in WHERE, ORDER BY, join conditions, and GROUP BY. Common reasons indexes are ignored:

Applying functions or expressions to indexed columns in WHERE.

Using a leading wildcard with LIKE (e.g., '%text').

Missing NOT NULL on indexed columns when the optimizer requires it.

Verify index usage with EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL).

3. Avoid Subqueries When Possible

Subqueries can cause repeated scans. The following MySQL example finds employees earning more than the department average using a subquery, and an equivalent join that materializes the average once.

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

Rewritten with a join:

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 average in a temporary table, reducing execution time. Similar transformations apply to IN and EXISTS clauses. In PostgreSQL the join may use a hash join, while Oracle and SQL Server often perform automatic subquery unnesting.

4. Do Not Use OFFSET for Pagination

Using LIMIT … OFFSET N forces the engine to scan and discard the first N rows, which becomes slower as N grows. A key‑set (or cursor) pagination pattern remembers the last retrieved key and queries for rows greater than that value.

-- Inefficient pagination
SELECT * FROM large_table ORDER BY id LIMIT 10 OFFSET 1000000;

-- Efficient key‑set 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.

5. Understand the Logical Execution Order of SQL Clauses

The logical order differs from the written order. The 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

Placing filters early (e.g., in WHERE rather than HAVING) reduces the amount of data processed. The order also explains common errors such as referencing column aliases in WHERE or selecting non‑aggregated columns not present in GROUP BY.

Example of an illegal reference:

SELECT emp_name AS empname
FROM employee
WHERE empname = 'Zhang Fei';  -- alias not yet defined

Example of a GROUP BY misuse:

SELECT dept_id, emp_name, AVG(salary)
FROM employee
GROUP BY dept_id;  -- emp_name not aggregated nor grouped

Correct approaches include using window functions or aggregating before selecting non‑grouped columns.

Conclusion

Effective SQL optimization requires understanding the optimizer, creating appropriate indexes, writing queries that guide the optimizer toward efficient plans, and avoiding constructs that force costly scans. Applying these five rules yields faster and more scalable database applications.