Indexes

Say you have a table with 10 fields, 50,000-100,000 records, and 1 primary key field.

Is there any performance difference between creating a "covered" index and versus creating 9 individual indexes (not 10 b/c i'm assuming the PK field will already have an index created for it), one for each non key field.What is a "covered" index?|||A covered Index is an index which includes many columns in it. For example if you have an application which can search on 5 out of 10 fields you could create an index on those 5 fields and the result is supposed to be quicker searches on those 5 fields.|||Then I guess it might depend on how often rows are inserted, and how often these columns are updated, since that is when index rows would be inserted or updated.

It sounded like you were suggesting putting all the columns of the table in one index -- this would not accomplish anything, would it?

I see what you are saying about combining some columns in an index, particularly if your search would be filtering on more than one of the columns.

It's difficult to make a suggestion without more info on activity, column size, and filtering methods.|||Assume that you'll be reading most of the time. Given that is there a difference between the two methods.. If any?|||Do we speak about read or write operations here?

If we speak read, then it's all coming about what queries are going to be used.

If there's gonna be a SELECT statement that will be utilizing a scope that will use a WHERE with 5 fields, the optimum is to have an index that will cover those 5 fields instead of having those 9 seperate indexes.

As for the write operation I am not that sure that there'll be any differences anyways.

I think it all comes to the query optimizer really and the execution plan.|||I've always thought you should cover the columns in a popular where clause.

In a few cases where the data is inserted or updated more than read, then fewer indexes is better.|||I think it depends on your query.
Also, the index tuning wizard may helpful in your case.