mysql where condition

Question

Hi,

I'm interested in where condition; if I write:

Select * from table_name 
where insert_date > '2010-01-03' 
and text like '%friend%';

is it different from:

Select * from table_name 
where text like '%friend%'  
and insert_date > '2010-01-03';

I mean if the table is very big, has a lot of rows and if mysql takes records compliant with condition " where insert_date > '2010-01-03' " first and then searches in these records for a word "friend" it can be much faster than from first search for "friend" rows and than look into the date field.

Is it important to write where condition smartly, or mysql analyze the condition and rewrites where condition in the best way?

thanks

Answer 1

I don't know about MySQL in particular, but typically this kind of optimization is left to the database engine, as which order is faster depends on indexes, cardinality of data, and quantity of data among other things.

Answer 2

No, the two where clauses should be equivalent. The optimizer should pick the same index whichever you use.

The order of columns in an index does matter though.

If you think the optimizer is using the wrong index, you could give it a hint. More often than not though, there's a good reason for using the index it has chosen to use, so unless you know exactly what you are doing, giving the optimizer hints will often make things worse not better.

Answer 3

I think it's true, that both of where clause ar similar in database abstraction

Answer 4

By definition, a logical conjunction (the AND operator) is commutative. This means that WHERE A AND B is equal to WHERE B AND A.

It makes no difference in which order you write your conditions.

However, what makes a difference is what indexes you have in place on your table. The query analyzer takes these into account. It is also smart enough to find the part of the condition that is easiest to check and apply that one first.