SQL Query Optimization Rules and Best Practices

SQL itself is easy to learn, but writing queries that run efficiently requires careful optimization.

Rule 1: Return Only the Needed Results

Always specify a WHERE clause to filter rows so the database can use indexes (B‑tree, B+‑tree) instead of performing full table scans; avoid SELECT * which forces reading all columns.

Rule 2: Ensure the Query Uses the Correct Indexes

Create indexes on columns frequently used in WHERE, ORDER BY, join conditions, and GROUP BY. Beware of expressions or functions on indexed columns, mismatched data types, and left‑wildcard LIKE patterns that can invalidate indexes. Use EXPLAIN to verify that the optimizer chooses the intended index.

Rule 3: Avoid Subqueries When Possible

Subqueries often cause nested‑loop execution and repeated scans. For example, the following MySQL query 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);

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 uses materialization and a single scan, yielding faster execution on MySQL, Oracle, SQL Server, PostgreSQL, and SQLite.

Rule 4: Do Not Use OFFSET for Pagination

Using OFFSET forces the database to skip an increasing number of rows, which becomes slower with large tables. Instead, remember the last retrieved primary‑key value and query the next page with a condition on that key.

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

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

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

Rule 5: Understand the Logical Execution Order of SQL Clauses

The logical order differs from the written order: FROM/JOIN → ON → WHERE → GROUP BY → HAVING → SELECT (with optional DISTINCT) → set operators (UNION, etc.) → ORDER BY → OFFSET/FETCH. Knowing this helps place filters early (in WHERE) and avoid errors such as referencing column aliases in WHERE or selecting non‑aggregated columns after a GROUP BY.

-- Wrong example: using alias in WHERE
SELECT emp_name AS empname
FROM employee
WHERE empname = 'Zhang Fei';

-- Wrong GROUP BY example: selecting non‑grouped column
SELECT dept_id, emp_name, AVG(salary)
FROM employee
GROUP BY dept_id;

Correct usage involves filtering before aggregation and only selecting grouped columns or aggregates, or using window functions when both details and aggregates are needed.

Conclusion

SQL optimization is fundamentally about understanding how the optimizer works, creating suitable indexes, writing queries that guide the optimizer toward efficient plans, and, when necessary, manually restructuring queries for better performance.