CODEWITHSUNDEEP
sqlmysqlperformance
javanix
javanix

Reputation: 1330

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

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.

Upvotes: 2

Views: 346

Answers (2)

Chris Morgan
Chris Morgan

Reputation: 2080

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.

Upvotes: 0

ChssPly76
ChssPly76

Reputation: 100706

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

Upvotes: 2

Related Questions