Does order of boolean statements make a performance difference in a MySQL query?

Question

Suppose I want to query a table based on multiple WHERE clauses.

Would either of these statements be faster than the other?

  SELECT * 
    FROM table 
   WHERE (line_type='section_intro' OR line_type='question') 
     AND (line_order BETWEEN 0 AND 12) 
ORDER BY line_order";

...or:

  SELECT * 
    FROM table 
   WHERE (line_order BETWEEN 0 AND 12) 
     AND (line_type='section_intro' OR line_type='question') 
ORDER BY line_order;

I guess what it would come down to is whether the first one would select more than 12 records, and then pare down from there.

Answer 1

No, the order does not matter. Query optimizer is going to estimate all conditions separately and decide on the best order based on what indexes are applicable / size of targeted selection / etc...

Answer 2

It depends on your indexes. If you have a multi- index on (line_type, line_order), the first query is faster. If you have an index on (line_order, line_type), the second one is faster. This is because for multi-column primary keys, MySQL can only do the comparisons in order. Otherwise, there is no difference.

HTH -

Chris