Does MySQL run WHERE clause before ORDER BY?

Question

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.

Answer 1

Yes it does.
....................

Answer 2

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).

Answer 3

Logically they are in the order indicated by @Lennart.

But your query is especially hard to optimize. There is no optimization for WHERE constant BETWEEN col_start AND col_end The best the Optimizer can do is use an index on either or both columns, but then risk scanning half the table.

The Optimizer may use INDEX(type_of_code) to help with the ordering and limiting of ORDER BY type_of_code ASC LIMIT 1. With luck, it will find a row matching the WHERE quickly; without luck, it might have to scan the entire table.

Can't win.

Have you supplied exactly the query in question? Let's see SHOW CREATE TABLE. Also, to the ranges overlap? (The Optimizer cannot make a useful assumption here, but you can.) There may be some tricks to speed up the query, even if it has to scan the entire 5M rows.