Wednesday, March 21, 2012

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.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.|||If I know that first_name and last_name will always be used should I group them both into one index?|||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.|||I guess the better question would be : "How do I redesign my table"

State 200, zip 100, ssn 50?

No comments:

Post a Comment