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 link.

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.

Answer 3

I have been working with SharePoint lists for a few year now and have experimented with indexes both single and compound with item counts in the hundreds and thousands (20k+).
And here is what I have observed.

1. The 5k (5,000) item limit is the limit specified for my particular SharePoint Online (SPO) instance and may different for other tenants.
2. Compound indexes did not enable me to submit a query using two fields, which together would have guaranteed fewer than 5k results when the first filter resulted in more than the 5k.
3. Sorting on a indexed lookup field resulted in a 5k+ query error.
4. Building indexes on empty fields appears to be the only way to future-proof a SPO list against field expansion once enough items exist.
5. It is technically possible to add new indexes up to the 20 limit by sorting a field after there are 5k+ fields, but planning ahead is always preferred.

For more information on SharePoint indexes you can review SharePoint Index Column – Complete tutorial for a "technical" explanation.