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?

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 6, 2014 at 6:36 am

  #1686573

  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: 323522

  More actions

  February 6, 2014 at 6:38 am

  #1686574

  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

  February 6, 2014 at 6:45 am

  #1686580

  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]