Adding an index to a column increases performance when you use filters. You can add indexes on up to 20 columns on a list or library.
While you can add up to 20 indexes per list or library, it's recommended you add indexes to only to the most commonly used columns and on those columns which you are using to filter data both in views as well as using the column headers. Indexes add overhead to the data so use them only where they make sense to be used.
If you've exceeded the List View Threshold and been blocked, you can normally still add indexes to columns, by default, when you have fewer than 5,000 (SharePoint Server 2013) or 20,000 (SharePoint Server 2016 and SharePoint Server 2019) items in your list or library. These values are configurable on the web application level in SharePoint by the farm administrator and could be set different for your environment. In SharePoint, you can manually add an index to a list of any size.
How to create a simple or compound index
To help choose which columns to index, note which columns are most commonly used across different views for filtering.
Important: Creating an index requires accessing all items in the list, so it's possible you may be blocked from creating an index for a column if the entire list exceeds the List View Threshold. See the second paragraph above for more information about these limits. If this occurs, do the operation during the Daily Time Window, or contact your administrator.
-
Select the name of the list or library in the navigation, or select Settings (or Site Actions in SharePoint 2010), select Site Contents (or View All Site Content in SharePoint 2010), and then click the name of the list or library.
-
Do one of the following:
For SharePoint Online or SharePoint 2019, select Settings , and then select List settings or Library settings.
In SharePoint 2016, 2013, or 2010, select List or Library, and then List Settings or Library Settings in the ribbon.
-
Scroll down to the Columns section.
Important: Although you can index a lookup column to improve performance, using an indexed lookup column to prevent exceeding the List View Threshold doesn't work. Use another type of column as the primary or secondary index.
-
Select Indexed columns.
-
On the Indexed Columns page, select Create a new index.
-
Do the following:
-
To create a simple index, select a column that is not a Lookup column in the Primary Column section, under Primary column for this index.
-
To create a compound index, select a lookup column as the primary column for this index. Use a different column that is not a Lookup column in the Secondary Column section, under Secondary column for this index.
Notes:
-
Be sure to select a supported column to index
-
If you are adding an index to avoid a List View Threshold error, ensure that the field used is not a lookup field. Please see supported column to determine which field types are lookup fields.
-
-
How to remove an index
If you want to remove an existing index from a column, select the index at step 5 above and then select Delete to remove it again.