| Author |
Topic |
|
mtl777
Yak Posting Veteran
63 Posts |
 Posted - 2009-12-31 : 11:53:53
|
Hi, which of the following is faster and why?SELECT * FROM MyTable WHERE Description LIKE 'IAM%'-- or --SELECT * FROM MyTable WHERE Left(Description,3) = 'IAM'I would like to know the answer in both cases where there is or there is no index on Description.Your help is greatly appreciated. Thanks in advance!  |
|
|
DP978
Constraint Violating Yak Guru
269 Posts |
Posted - 2009-12-31 : 12:15:37
|
| Changing my answer here - It appears that if you do no manipulations on columns SQL has a chance to Index said columns, so LIKE can win out in some cases.By doing a LEFT( you essentially are manipulating the data and preventing any auto-indexing.Source: http://okdw.com/Community/forums/p/16160/91092.aspxEdit: Someone else will be able to provide more specifics im sure :) |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-12-31 : 12:31:14
|
| LIKE will perform fewer reads |
|
|
|
jcampbell
Starting Member
9 Posts |
Posted - 2009-12-31 : 13:35:13
|
| functions in where clauses are usually going to be slower. If there is an index on the field then the left will do an index scan and the like will do an index seek which is more efficient then the scan.Be aware there are a lot factors that determine if the optimizer decides to use an index, including what fields are in the select list, If for example you did a select * from...., the optimizer will most likely decide to not use the index and do a clustered index scan. |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2009-12-31 : 15:22:08
|
| Thank you so much for the replies! If LIKE 'IAM%' is used, do you need a full-text index on Description in order to do an index seek? What happens if your index is not a full-text index? Is SQL Server intelligent enough to know that a full-text index is not needed in this case since the string to be searched is at the beginning and not at the middle or end of the Description? |
|
|
|
jcampbell
Starting Member
9 Posts |
Posted - 2009-12-31 : 18:08:43
|
| you do not need full text indexing to use like and take advantage of the index for index seeks, you will use full text indexing for the following types of advance searches * Two words near each other * Any word derived from a particular root (for example run, ran, or running) * Multiple words with distinct weightings * A word or phrase close to the search word or phrase |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2009-12-31 : 21:06:39
|
| So full-text indexes are used for fuzzy or similar advanced searches and not necessarily whenever LIKE is used. Thanks for the heads up! |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-01-01 : 08:53:48
|
| russell , could you explain further by what you mean "LIKE will perform fewer reads" . If there was a scenario where no indexes where available - wouldn't both do a full table scan? I'm interested to know about what you mean by fewer readsJack Vamvas--------------------http://www.ITjobfeed.com (IT jobs) |
|
|
|
mtl777
Yak Posting Veteran
63 Posts |
Posted - 2010-01-04 : 17:17:07
|
| I'm interested in the answer to this, too. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-04 : 17:41:15
|
| if there's no index then of course it will scan either way.but if the field is indexed, like will utilize the index (assuming there's no leading wild-card) while left(column, x) will not.set statistics io on then run both queries. on a relatively large table, the results, in terms of reads, are dramatic.i always like to say that the practice of query tuning is really the practice of reducing reads. |
|
|
|
|
LIKE vs LEFT - which is faster?