Add an index to a list or library column
Applies To
SharePoint Server Subscription Edition SharePoint Server 2019 SharePoint Server 2016 SharePoint Server 2013 SharePoint Server 2013 Enterprise SharePoint in Microsoft 365 SharePoint Server 2010Adding an index to a list or library 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 filter column data in a list or library, see Use filtering to modify a SharePoint view.
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 in Microsoft 365 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.
-
-
Select Create.
For ideas on planning your libraries initially, see Introduction to libraries
-
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.
Supported and unsupported columns for indexing
Supported Column Types
|
Unsupported Column Types
|
Metadata Navigation and Filtering is enabled by default on most SharePoint sites. Even if metadata navigation has not been configured for a specific list or library, Metadata Navigation and Filtering is still at work behind the scenes to improve the performance of views. The Metadata Navigation and Filtering feature can automatically select the best index to work every time a view is loaded. When you load new views, apply filters to views, clear filters, or apply a sort on a field, query optimization determines the best way in which to query the database.
If a user creates or loads a view that cannot use an index to query the list, then Metadata Navigation and Filtering will construct and execute a fallback query. A fallback query is a modified version of the original user query that displays a partial set of the items requested because it queries against only a portion of the list instead of the entire list. It is intended to provide you with some useful results in circumstances when the original query is blocked due to large list throttling. Up to 1,250 of the newest items are displayed based on when those list items were added to the list. Occasionally, fallback queries will return 0 results if no items in the part of the list scanned by the query contain results that match the original user query.
For more info, see Set up metadata navigation for a list or library.