Should i index all columns in a list?

Question

I have a screen where the user will be querying using all columns in the list. Should i index every column.

What are the rules around this?

Would there be any performance problems?

Answer 1

NO, Index the columns that you search and filter on, try and stay at a maximum of 2.

This works exactly like a database, the more indexes the slower updates and additions become.

Columns that you might want to index are:

Any column that appears in JOINS or WHERE statements.

Answer 2

As you probably are aware use of Indexing is meant to increase performance, particularly in querying, filtering and sorting in large lists, avoiding database scanning, etc. But also over-doing could fall in other extreme (beyond the actual limit of 20), e.g. remember that each update in the structure would require adding a row in NameValuePair table in Content database, which grows extremely fast. Have a look at this http://blog.dynatrace.com/2009/01/28/sharepoint-list-performance-how-list-column-indices-really-work-under-the-hood/

You should understand the fact the there are Simple or Compound, as a rule: - Create single indexes for a single field to filter e.g. with the CQWP and create compound indexes for queries that are going to filter two columns.