Add an index to a SharePoint column

Add an index to a SharePoint column

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 Online, 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.

  1. Select the name of the list or library in the navigation, or select Settings  Office 365 Settings button (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.

    Settings menu with Site Contents highlighted
    View all site content on the Site actions menu

  2. Do one of the following:

    For SharePoint Online or SharePoint 2019, select Settings  Office 365 Settings button , and then select List settings or Library settings.

    Settings menu with List settings highlighted

    In SharePoint 2016, 2013, or 2010, select List or Library, and then List Settings or Library Settings in the ribbon.

    List Settings on ribbon
  3. 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.

  4. Select Indexed columns.

    Indexed columns link on the List or Library settings page
  5. On the Indexed Columns page, select Create a new index.

    Indexed columns page with Create a new index highlighted
  6. Do the following:

    1. 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.

    2. 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.

      Edit index page with column selected from drop down box
    3. 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. 

You can delete an index after clicking on it

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

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.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×