Advanced · Lesson 14 of 14

Execution order and slow queries

Why queries behave and how to speed them up.

The logical order

FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> WINDOW -> SELECT -> DISTINCT -> ORDER BY -> LIMIT. Memorize this once and endless mini-mysteries (why no alias in WHERE) resolve themselves.

Why a query is slow

Large scans, missing indexes, functions applied to indexed columns, subqueries with correlated executions, and unnecessary big LIMITless reads. Naming two causes is usually enough.

Indexes in one breath

An index is a sorted structure that turns a full scan into a narrow lookup. It speeds reads, slows writes, and only helps if the query filters on indexed columns.

Examples

Plan of a simple query

Filter rows, then groups, then sort. Read it in execution order.

SELECT d.name AS dept, AVG(e.salary) AS avg_salary FROM employees e JOIN departments d ON d.department_id = e.department_id WHERE e.salary > 100000 GROUP BY d.name HAVING AVG(e.salary) > 150000 ORDER BY avg_salary DESC;