Forum Discussion
Limitations of Predictive Indexing
MicrosoftHey everyone,
As of Spring of 2018, you can add/remove indexes from lists of any size in SPO. Having more than 20,000 items should no longer block adding/removing of indexes. We are working on enabling predictive indexing of lists larger than 20K items as well so that any views for these lists will automatically add indexes in the background.
Adding indexes on the fly when you sort by an unindexed column will only work for lists smaller than 20,000 items for the moment. We are trying to see how we can make that happen in the coming months.
In SharePoint Online, we have two document libraries with 7,000 and 12,000 items respectively, and we are unable to use indexes to sort or filter on the following column types: managed metadata, name (e.g. Modified By).
We have tried the following: created an index for these column types prior to upload of files; created a view with a sort against one of these column types and then added files, triggering automated indexing; created an index after upload of files. In all of these cases, we are unable to sort by these column types, and if we switch to a view which sorts against these column types, we get an error referencing the list view threshold.
We are successfully able to do both things with indexed columns based on date or text.
Is there any known limitation for these field types in reference to indexing? Would the method of import (all at once via API) be a potential source of the issue? I doubted the latter since the behavior is not consistent across column types.
Thanks for your help with this!
- Kerem Yuceturk
Hi micro99999, this is indeed a limitation for these types of columns due to how they are implemented. Here's the relevant piece from our support article:
Columns with column types people, lookup or managed metadata can cause list view threshold errors when sorting. However, text, number, date and other column types can be used in the first sort.
Hopefully it is possible to work around this by sorting by ID or another field of type text, number or date, and then using filters for these values?
Thanks for the reply. Just to verify, is it never possible to sort by managed metadata columns regardless of sort settings or indexes? For instance, the documentation states: "However, text, number, date and other column types can be used in the first sort." Does this mean a secondary sort may work?
