Clever Geek Handbook

Does the order of boolean operators maintain the performance difference in a MySQL query?

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

Would one 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 this will happen if the first one will select more than 12 entries and then backtrack from there.

+2


source to share


2 answers


No, the order doesn't matter. The query optimizer will evaluate all conditions separately and choose the best order based on which indexes are applicable / target selection size / etc.



+2


source


It depends on your indices. If you have multi-index (line_type, line_order) the first query is faster. If you have an index (line_order, line_type) the second is faster. This is because for primary keys with multiple columns, MySQL can only perform comparisons in order. Otherwise, there is no difference.

HTH -



Chris

0


source






More articles:


All Articles