Order of WHERE constraints in query

Question

Possible Duplicate:
Does the order of fields in a WHERE clause affect performance in MySQL?

Does the order of the elements in a WHERE clause change the speed of the query?

I have the following:

SELECT * FROM table1 WHERE a<b

. This returns 500 records.

SELECT * FROM table1 WHERE c<d

. This returns 130000000 records.

Now, if you have a choice between:

SELECT * FROM table1 WHERE a<b AND c<d

or

SELECT * FROM table1 WHERE c<d AND a<b

Which of these 2 queries will be faster? Does the order matter?

But doesn't mysql do the constraints one after the other. In which case I would guess the first will be faster because it leaves less entries for the second constraint to work on? — David19801, Oct 31 '12 at 13:35

score 3 · Answer 1 · answered Oct 31 '12 at 13:35

3

The only time it would affect performance is with indexes.

If you have an index on (a, c), a predicate of WHERE c < d AND a < b would run slower since the index goes left to right.

If you do not have an index, the order does not matter.

answered Oct 31 '12 at 13:35

Kermit

33,827
13
85
121

Absolutely. The speed of the query well depend on the indices of the table and the engine the table is running on. – deed02392 Oct 31 '12 at 13:36
Removed downvote following edit but will be surprised if this is correct and MySQL can't rearrange predicates as it sees fit. – Martin Smith Oct 31 '12 at 13:40
@MartinSmith Thanks. I'm more versed with SQL Server performance, and I know for a fact the index will *not* be used if the order doesn't go with the 'left-to-right' of an index. – Kermit Oct 31 '12 at 13:41
1

The question is asking about `c < d AND a < b` vs `a < b AND c < d` though. They are the same predicates just the order reversed. In SQL Server it definitely does not make any difference what order they appear in the query. – Martin Smith Oct 31 '12 at 13:43
@Martin How does mysql decide which to do first? Does it precompute the expected number of rows for each constraint? how does it know? – David19801 Oct 31 '12 at 13:44
@MartinSmith [This excerpt](http://books.google.com/books?id=FcW6hYmH6FgC&pg=PT176&lpg=PT176&dq=%22Furthermore,+while+creating+an+index+on+multiple+columns,+which+is+also+referred+to+as+a+composite+index,+column+order+matters.%22&source=bl&ots=gSHachxYep&sig=SIPalKYa-5758_n_Imp-pYVvtFU&hl=en&sa=X&ei=pCuRUIq_H_KvygHe4oC4Ag&ved=0CB8Q6AEwAA) is what I'm going off of. – Kermit Oct 31 '12 at 13:47
2

@njk - Yes column order matters **in the index definition** (if you have an index on `last name, first name` it won't help queries on first name) but the question is about rearranging **the same** predicates in the query. SQL Server wouldn't care if you wrote the query as `last-name='foo' and first_name='bar'` or `first-name='bar' and last_name='foo'` – Martin Smith Oct 31 '12 at 13:49
@David19801 I would recommend reading [this answer](http://stackoverflow.com/a/4035997/679449) regarding how the optimizer works. – Kermit Oct 31 '12 at 13:49
@MartinSmith I'm in agreement. – Kermit Oct 31 '12 at 13:53

score 0 · Answer 2 · answered Oct 31 '12 at 13:34

0

No, there is no difference. Just like in MATH, A + B = B + C. It just means that interchanging both doesn't make any difference.

answered Oct 31 '12 at 13:34

John Woo

258,903
69
498
492

I believe he is talking about the speed of the query, not the result set. – Darrrrrren Oct 31 '12 at 13:35
@Darrrrrren i didn't say about result set. I just illustrate that interchanging both don't have any difference. – John Woo Oct 31 '12 at 13:36

score 0 · Answer 3 · edited May 23 '17 at 11:55

0

I don't think so there will be any difference, it will be optimalized by the sql anyway. More info here at this question: Does the order of fields in a WHERE clause affect performance in MySQL?

edited May 23 '17 at 11:55

Community

1
1

answered Oct 31 '12 at 13:36

Dolfa

796
4
21

Order of WHERE constraints in query

3 Answers3