Index Usage

Question

Index Usage

Tara-1044200

SSCoach

Points: 15951
More actions
August 4, 2009 at 1:57 pm

#213121

I wounder if an Index is used when my query has " like operator" on a column which has an index.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22411 More actions · Answer 1

it can be used pretty much directly if the like is of the form "column like 'begins with%'"

if its like this: "column like '%something%'" then it can't be used as an index but the query optimizer may go ahead and do an index scan vs. a table scan if there is less I/O involved.

The probability of survival is inversely proportional to the angle of arrival.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

It can depend. If the LIKE statement resembles:

MyColumn LIKE 'value%'

Then it's possible it may use the index as a scan.

On the other hand -

MyColumn LIKE '%value%'

has NO shot at using the index. it will then revert to a table scan.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SanjayAttray SSChampion Points: 13157 More actions · Answer 3

Agreed with Matt. Rule of thumb: Never use like operator on a very large table. It kills.

SQL DBA.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

So.. Finally like 'column%' is better than like'%column%' but i think both of then scan the table instead seeking though i have index on column

As you guys said if like opreator kill performance what if i MUST use it, is there any workaround(any other T-SQL code to replace) for large tables.

sturner SSC-Insane Points: 22411 More actions · Answer 5

Tara, Just because there is an index on a column does not mean it will be used. It depends upon the query plan and the selectivity of the index based on statistics.

As far as the comment "like should not be used because it kills performance.." is sort of a stupid statement. LIKE is a powerful TSQL operator and comes in extremely handy (even mandatory) for many situations. We use it to scan a massive table for certain bad characters and poorly formed addresses.

We know these jobs scan the entire table but there is no faster way to lexically analyze character strings than deep in the core of the engine, and that is what LIKE does. If you are searching for multiple targets it is best to join to a small table of search terms using like instead of using a lot of OR this OR that stuff.

As always, proper database design, normalization and choice of type and size of datatypes (i.e. avoid nvarchar()) is the key to good performance.

The probability of survival is inversely proportional to the angle of arrival.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

Try and have as good a WHERE clause as possible.

"Keep Trying"

MartinSchmidt Mr or Mrs. 500 Points: 525 More actions · Answer 7

If you do need the functionallity, and the like kills your performance.

I would then have a look at Fulltext Seach

/mSc

Tara-1044200 SSCoach Points: 15951 More actions · Answer 8

i never used FULL TEXT SEARCH, how does it work in the queries.

thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SanjayAttray (8/4/2009)
Agreed with Matt. Rule of thumb: Never use like operator on a very large table. It kills.

More correctly, never use LIKE with a leading wildcard on a large table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

GilaMonster (8/5/2009)
More correctly, never use LIKE with a leading wildcard on a large table.

Absolutely. It's good to be careful with queries like these, but if it uses the index, it might not be bad. Assuming the pattern is selective enough, a partial scan of the index (especially if it's the clustered index or if it's covering) might be exactly what's needed.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Yeah but likes with leading wildcards will always, in the absence of other SARGable predicates do a full index/table scan

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

doh... there I go picking up the wrong part of the quote. I was actually commenting on the "be afraid of the index scan" part of the conversation.

(I agree that the lead % will never using an index - just kind of strayed from the original intent).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?