Does MySQL run WHERE clause before ORDER BY?

I have this query:

SELECT my_code 
FROM ranges 
WHERE 123456789 BETWEEN first_number AND last_number 
ORDER BY type_of_code ASC LIMIT 1 ;

My question is, will it run the ORDER BY after it has selected rows from the WHERE clause?

I have KEY on first_number and on last_number. My table is around 5 million rows. I do not wish to ORDER BY first.

The logical order in which a query is evaluated is:

• FROM / JOIN clause

• WHERE clause

• GROUP BY clause

• HAVING clause

• SELECT clause

• ORDER BY clause

so from a logical point of view, yes the WHERE should be evaluated before the order by.

But the DBMS:s (in this case MySQL) is allowed to evaluate the query in any order as long as the result of the query is preserved. So in a future version it might evaluate a query in another evaluation order which may cause unexpected performance effects.

Now, since ORDER BY basically transforms the query into a cursor I think it is pretty safe to assume that the WHERE clause will be evaluated before the ORDER BY (at least as long as the table is row-based, for a column-based table the situation may be different).

StackExchange Database Administrators Q#162743, answer score: 7