2

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.

  • 1
    Did you have a chance to look at the query plan? – mustaccio Jan 31 '17 at 14:03
0

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

  • Perfect, so knowing that the where clause will maximum return 3 records does not de-perform due to the order by clause. – Michael Nielsen Jan 31 '17 at 14:10
4

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

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.