CODEWITHSUNDEEP
sqlmysqlmicro-optimization
An employee
An employee

Reputation: 6298

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

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

Upvotes: 1

Views: 143

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146557

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.

Upvotes: 0

knittl
knittl

Reputation: 265678

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

Upvotes: 0

Eric
Eric

Reputation: 95163

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

Upvotes: 2

Matt
Matt

Reputation: 359

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.

Upvotes: 0

Related Questions