If you run into performance issues with your library views, or worse still, into the 5000-item limit on library views, Microsoft’s recommendations quickly point you in the direction of column indexes. SharePoint Online at the moment has even started automatically creating such indexes for you if it feels that will help your library views (just switch on Automatic Index Management in the advanced list settings).
That’s not bad of course, but there are some serious limitations when it comes to indexing columns. You can have a maximum of 20 indexes, but that seems plenty to me. The major issue is that several column types cannot be indexed. The one that I find most annoying is the lack of indexing for multi-value columns (no matter if it’s a Choice or Lookup column).
Here’s a quick overview:
Supported Column Types
- Single line of text
- Choice field – as long as it’s single value
- Lookup – as long as it’s single value
- Person or Group – as long as it’s single value, because this is essentially a lookup column
- Managed Metadata
Unsupported Column Types
- Title – only supported for indexing in Lists
- Multiple lines of text
- Hyperlink or Picture – because for SharePoint this is a lookup
- Any custom field types
- Calculated Field
- Multi-value choice
- Multi-value lookup
- External data
The fact that the above types cannot be indexed can have serious implications, especially if you have reached your 5000-item limit. Because above that limit, SharePoint will only allow filtering of data on indexed columns. So if you happen to have a few columns that are important to you for filtering, but they belong to one of the above column types, you’re in trouble. And there’s really no realistic escape route. Metadata navigation only gets you so far. It has a fallback query built in that should limit the result set automatically, but I’ve seen cases where even the fallback query just ran endlessly, showing zero results.
As a side note: The multi-value choice or lookup columns seem to be a more general problem in Office 365 related products. I recently read they also are an issue in PowerBI (http://sympmarc.com/tag/multiselect-columns/).
Here’s the official Microsoft documentation on indexing:
And this is another source I found very useful: