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?
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It only takes a minute to sign up.Sign up to join this community
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.
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.