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. Indexes add overhead to the data.

If you've been blocked by exceeding the List View Threshold, you can normally still add indexes to columns when you have fewer than 20,000 items in your list or library.

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 used 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, do the operation during the Daily Time Window, or contact your administrator.

  1. Click the name of the list or library in the left nav bar, or click Settings Office 365 Settings button or Site Actions, click Site Content or View All Site Content, and then click the name of the list or library.

    Settings menu with Site Contents highlighted
    Getting to site content in SharePoint Online, 2016, or 2013
    View all site content on the Site actions menu
    Getting to site content in SharePoint 2010

  2. Do one of the following:

    For SharePoint Online, click Settings SharePoint 2016 Settings button on title bar. , and then click List settings or Library settings.

    Settings menu with List settings highlighted

    In SharePoint 2016, 2013, or 2010 click 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. Click Indexed columns.

    Indexed columns link on the List or Library settings page
  5. On the Indexed Columns page, click 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 different column that is not a Lookup column in the Secondary Column section, under Secondary column for this index.

      Note: Be sure to select a supported column to index.

      Edit index page with column selected from drop down box
    3. Click Create.

    For ideas on planning your libraries initially, see Plan document libraries in SharePoint

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)

  • Managed Metadata

  • Yes/No

  • Lookup

Unsupported Column Types

  • Multiple lines of text

  • Choice (multi-valued)

  • Calculated

  • Hyperlink or Picture

  • Custom Columns

  • Person or Group (multi-valued)

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

We're listening

Created April 29, 2017

Was this article helpful? If so, please let us know at the bottom of this page. If it wasn't helpful, let us know what was confusing or missing. Please include your SharePoint, OS, and browser versions you're using. We'll use your feedback to double-check the facts, add info, and update this article.

Connect with an expert
Contact us
Expand your skills
Explore training

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.

×