Add an index to a list or library column

Adding 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 20,000 items in your list or library. 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, 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. If this occurs, contact your administrator.

Select the name of the list or library in the navigation, or select Settings   , select Site Contents, and then click the name of the list or library.

select Settings   , and then select List settings or Library settings.

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.

 

Select Create.

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

  • Single line of text
  • Choice (single value)
  • Number
  • Currency
  • Date and Time
  • Person or Group (single value) (Lookup)
  • Managed Metadata (Lookup)
  • Yes/No
  • Lookup (Lookup)

Unsupported Column Types

  • Multiple lines of text
  • Choice (multi-valued)
  • Calculated
  • Hyperlink or Picture
  • Custom Columns
  • Person or Group (multi-valued) (Lookup)
  • External data

Using indexed and fallback queries

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.