Index with a column varchar(50)

Question

Index with a column varchar(50)

sistemas_casinomnes

SSChasing Mays

Points: 659
More actions
February 6, 2014 at 6:34 am

#151318

Is recommended that créate an index with a column that is varchar?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

It depends...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

the usual answer is it depends on how you are searching;

if you are searching on last name (WHERE LName = 'McArther')

then yes, an index would help a query like that.

anything that starts with would also benefit form the index: ie

LIKE 'MC%'

but if you are looking for substrings, then the index would not be used, a table scan would occur instead:

LIKE '%the%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

Lowell (2/6/2014)
the usual answer is it depends on how you are searching;
if you are searching on last name (WHERE LName = 'McArther')
then yes, an index would help a query like that.
...

It also depends on data density and distribution. Let say you have 1,000,001 rows in your table and 1,000,000 of them have LName = 'McArther' for one or another reason. Index will not help you much in this case...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]