1

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

Eric
  • 92,005
  • 12
  • 114
  • 115
An employee
  • 6,188
  • 9
  • 33
  • 43
  • As others have stated, MySQL doesn't care about the order. But I remember on Oracle (a long time ago, maybe it's not an issue anymore) having to play around with the order of the where clause to get it to use the right index. I sure don't miss those days... – nathan Sep 09 '09 at 14:29

4 Answers4

2

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

Eric
  • 92,005
  • 12
  • 114
  • 115
0

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.

Matt
  • 349
  • 1
  • 10
0

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?)

knittl
  • 246,190
  • 53
  • 318
  • 364
0

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.

Charles Bretana
  • 143,358
  • 22
  • 150
  • 216