To Index or not to Index, that is the question!

[GoldCoast_Nerd]

hey,
i have a massive cuctomer table that has a lot of rows. the customerID is the primary key, so sql server makes a clustered index on that .
we run a lot of queries that use the countryvalue, age and gender in the where clause to retrieve members for search results. the question is should i put a non-clustered index on these three columns so that the search results will be faster, on the testing environment the queries r faster, but i'm just worried that the table gets a lot of inserts and updates, hw will the index affect the performance then when there's so many inserts and updates
won't sql server have to rebuild the index everytime there is and insert or update??
any help greatly appreciated....
thanks..:)

[felgall]

Indexing gender would not be worth it because there are too few values the field can have. Unless everyone is about the same age, indexing on their age may be worthwhile if you do a lookup on that basis since it should dramatically reduce the portion of the database that needs to be read to return the requested results.

[GoldCoast_Nerd]

thanks for the reply,
so if i just index country value(that has the country names in it) and age, the queries should be a lot faster.
they did run a lot faster while testing, but just coz the table has heaps of inserts and updates, i'm not sure if to make the index in the production database..
do u reckon its safe and won't slow down the server coz it has to rebuild the index with every insert or update??
:confused:

[mattyblah]

trying to explain indexes can get a little difficult,because with sql server, it will go with what it feels is best performance (yes you can force it otherwise, but is NOT RECOMMENDED). learn to read the query execution plan. nothing will help you as much as this. g/l.