Placing index columns on the left of a mysql WHERE statement?

Question

I was curious since i read it in a doc. Does writing

select * from CONTACTS where id = ‘098’ and name like ‘Tom%’;

speed up the query as oppose to

select * from CONTACTS where name like ‘Tom%’ and id = ‘098’;

The first has an indexed column on the left side. Does it actually speed things up or is it superstition?

Using php and mysql

Answer 1

This is purely superstition. I see no reason that either query would differ in speed. If it was an OR query rather than an AND query however, then I could see that having it on the left may spped things up.

Answer 2

Check the query plans with explain. They should be exactly the same.

Answer 3

interesting question, i tried this once. query plans are the same (using EXPLAIN).

but considering short-circuit-evaluation i was wondering too why there is no difference (or does mysql fully evaluate boolean statements?)

Answer 4

You may be mis-remembering or mis-reading something else, regarding which side the wildcards are on a string literal in a Like predicate. Putting the wildcard on the right (as in yr example), allows the query engine to use any indices that might exist on the table column you are searching (in this case - name). But if you put the wildcard on the left,

select * from CONTACTS where name like ‘%Tom’ and id = ‘098’;

then the engine cannot use any existing index and must do a complete table scan.