Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146

    Unanswered: indexing suggestions

    I'm looking for some help on how i should index this table.

    current table has about 500k records in it.
    the fields in the table are:
    member_num (varchar(12), not null)
    first_name (varchar(20), null)
    last_name (varchar(20), null)
    ssn (varchar(50), null)
    address1 (nvarchar(200), null)
    address2 (nvarchar(200), null)
    city (nvarchar(200), null)
    state (nvarchar(200), null)
    zip (nvarchar(100), null)
    phone1 (nvarchar(50), null)

    all of the fields are searchable through an asp.net webform.

    my first stab at this consisted of creating a clustered index on member_num and then creating a separate index for each of the remaining fields.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I generally take a look at how the actual usage pans out. Do you expect the same amount of queries on address2 as last_name? Then there is the question of how often is last_name queried with or without first_name? Once you know how the searches usually shape up, then you can index smarter.

  3. #3
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    If I know that first_name and last_name will always be used should I group them both into one index?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Yes. I would bet that Last_Name will be more commonly used on its own, than first_name alone, so make Last_Name first in the index.

    Almost suffered a zen moment in there. Some thing like last shall be first...Must need more coffee.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess the better question would be : "How do I redesign my table"

    State 200, zip 100, ssn 50?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •