I'm having some trouble understanding Indexing and how to avoid the List View Threshold of 5000 items.
I created an Index for my Status field, which is what I use to specifically filter my default View to only show Active tickets on my list. Unfortunately despite this View only showing 74 items, I'm still getting the warning that I'm nearing the threshold (currently 4806).
Am I missing a step which will somehow allow Indexing to help lower my list view threshold number?
In SharePoint threshold issue is an impending issue for more than a decade. In order to deal with this issue first we need to understand what is indexing in database and how does index work in SharePoint, then need to see how to create indexing column, then need learn how to create dynamic filter column with today function....... so please go through these articles where you will clear about the indexing concept and how to fix this threshold issue.
What is database indexing in SharePoint?
A database index is a data structure that improves the speed of data retrieval operations from a database table and it works based on key value pair technique – the core binary search and linear search algorithms are built on the indexing technique.
In other words, we can say – an index is a copy of selected columns of data from an original table that can be searched very efficiently, which establish a direct link to the complete row of data from where it was copied from. Again, there are various types of indexing in database like cluster and non cluster, etc. I have not been going into that since the purpose of this article is different. In a short note – indexing is a technique using which we can get the fastest query result from database table. Indexing costs us additional writes and storage space in the file system.
In SharePoint there is a single table, i.e. “AllUserData” in all content database which stores all SharePoint list items I mean this is a common table for all the lists in the site collection. There are lots of internal columns in the table than what the number columns we create in SharePoint list – so what we see in the list it just a few columns. For example: 64 nvarchar, 16 units, 12 floats and so on.
If we look at the Microsoft documentation on the “AllUserData” table structure, we will be surprised to see how complex it is. Please click here to know more about “AllUserData” table structure.
How does indexing column work in SharePoint?
The list items are stored in the “AllUserData” table in the SQL. For every defined indexed column the SharePoint stores the index value for every list item in a separate table, i.e. “NameValuePair ” table which we have seen in the above. Let’s say we have 20,0000 items in the list, which means that we have 20,000 rows in “AllUserData” and 20,000 additional rows in the “NameValuePair” table (used for indexing).
So the SharePoint list items view requests are served based on the key value in the “NameValuePair” table and pick the appropriate mapping items from the main “AllUserData” table which is the actual data source. This is how the SharePoint indexing column speeds up the query performance.
Database indexing Example:
Learn more about what exactly SharePoint column indexing:
How to fix list view 5000 threshold issue :
We can increase the list items limit from 5000 to 50000(fifty thousands) in SharePoint on-premise in fact we can disable the list view threshold limit which is not all recommended but in SharePoint online we don’t have control over it, so we need to move on with the whatever limitation is set i.e. 5000.
Here based on my experience I will list out some some tips and tricks to avoid this issue which will work both for SharePoint online and on-premise.
Planning – before creating the list or library, analyze the data and plan accordingly. Try to split the data in multiple list or library.
Create multiple views and add “AND” condition in the list filter criteria, never add “OR” condition, “OR” condition will try to get the all data.
Create index column – we can only have 20 indexed columns in a given list. so we need plan this thoughtfully!
We can not create an indexed column if the list is already hitting
the list view threshold limit.
Maintain the Archive list – here we need to review the list items
periodically and need to check whether some items can be deleted or
archived to another list – here we can write some automation job
which will be move the items periodically to another archive list
automatically.
In the list filter criteria we need to filter the list items such a
way that at any given point of time list view will return less than
5000 items using filter on created column column technique(please see
the explanation in the below).
Create filter [Today]- “number of days technique:
Here one of the technique is – create a filter on “Created” column and use the [Today]- “number of days”with less than or equal to condition, something like below:
Notes:
The logic behind the filter on created column is – this will try to get the items which will return less than 5000 items. If the this condition also returns more than 5000 items, we need to split this up created column condition with multiple “AND” condition with the combination of some other column to ensure the filter returns less than 5000 items as an below example – though the implementation of this technique is time consuming.
Reference URL : How to fix listview 5000 threshold error
Reference URL - How to create Indexed column in SharePoint?
https://global-sharepoint.com/sharepoint-online/how-to-create-indexed-column-in-sharepoint/
