Understanding MySQL Query Execution Order: From FROM to LIMIT

MySQL processes a SELECT statement in a specific order, creating a series of virtual tables that feed into the next step. The processing sequence is: FROM – generates virtual table VT1 from the left and right tables (Cartesian product). ON – filters VT1 using the join condition, producing VT2. JOIN – applies the join type (e.g., LEFT JOIN, RIGHT JOIN) to add matching rows, resulting in VT3. If multiple tables are listed, steps 1‑3 repeat for each additional table. WHERE – filters VT3 according to the WHERE condition, yielding VT4. GROUP BY – groups VT4 by the specified columns, creating VT5. CUBE or ROLLUP – performs cube or rollup on VT5, producing VT6. HAVING – filters VT6 with the HAVING condition, resulting in VT7. SELECT – selects the target columns from VT9 (after distinct), forming VT8. DISTINCT – removes duplicate rows from VT8, producing VT9. ORDER BY – sorts VT9 according to the ORDER BY list, creating VT10. LIMIT – extracts the specified number of rows from VT10, yielding VT11, which is returned to the user.

The first clause processed is always FROM, and the final operation is LIMIT; each step creates a virtual table that is transparent to the user, with only the final virtual table being returned.