Advertisements

Add an index to a SharePoint column

Applies to: SharePoint Online, SharePoint Server 2016, SharePoint Server 2013 Enterprise.

Applying an index to a column boosts performance when you use filters. You can create indexes on up to 20 columns on a list or library. While you can insert up to 20 indexes per list or library, it’s recommended you optimise indexes merely to the frequently used columns. Indexes supplement overhead to the data.

If you’ve exceeded the List View Threshold and been blocked, you can usually still insert indexes to columns with under 20,000 items in your list or library. In SharePoint Online, you can manually produce 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 decide which columns to index, record the most commonly columns across multiple views for filtering.

Important: Forming an index requires accessing all items in the list, so there is a chance you could be blocked from making an index for a column if the whole list exceeds the List View Threshold. If this happens, do the operation during the Daily Time Window, or contact your administrator.

  1. Select the name of the list or library in the left nav bar, or press Settings or Site Actions, choose Site Content or View All Site Content, and then pick the name of the list or library.
View all site content on the Site actions menu
  1. Choose the name of the list or library in the left nav bar, or click Settings or Site Actions, press Site Content or View All Site Content, and then select the name of the list or library.
  1. Do one of the following: For SharePoint Online, select Settings, and then pick List settings or Library settings. In SharePoint 2016, 2013, or 2010 press List or Library, and then List Settings or Library Settings in the ribbon.
List Settings on ribbon
© Microsoft 2019
  1. Scroll down to the Columns section,.

Important: Although you can index a lookup column to enhance performance, using an indexed lookup column to impede passing the List View Threshold is ineffective. Use a different type of column as the primary or secondary index.

  1. Pick Indexed columns.
Indexed columns link on the List or Library settings page
© Microsoft 2019
  1. On the Indexed Columns page, select Create a new index.
Indexed columns page with Create a new index highlighted
© Microsoft 2019
  1. Do the following:
  • a. To create a simple index, choose a column that is not a Lookup column in the Primary Column section, under Primary column for this index.
  • b. To formulate a compound index, click a lookup column as the primary column for this index. Use  an alternative column that is not a Lookup column in the Secondary Column section, under Secondary column for this index.

Notes:

  • If you are inserting an index to avoid a List View Threshold error, ensure that the field used is not a lookup field. Please review supported column to influence determine which field types are lookup fields.
Edit index page with column selected from drop down box
© Microsoft 2019
  • c. Press Create.

For ideas on planning your libraries initially, see Introduction to libraries

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 automatically activated on the majority of SharePoint sites. Even if metadata navigation has not been set up for a particular list/library, Metadata Navigation and Filtering is still operating in the background to enhance the performance of views.

The Metadata Navigation and Filtering feature can intuitively pick the optimal index to function each time a view is loaded. When you load new views, integrate filters with views, clear filters, or apply a sort on a field, query optimisation determines the most effective 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 develop and execute a fallback query. A fallback query is a revised version of the original user query that shows a partial set of the items requested because it queries against only a section of the list instead of the entire list.

It aims to supply you with some valuable results in situations when the initial query is blocked due to large list throttling. Up to 1,250 of the recent items are presented based on the time they were added to that list.

Occasionally, fallback queries will return 0 results if no items in the part of the list scanned by the query include results that equal the first user query.

For more info, see Set up metadata navigation for a list or library.

Advertisements

Leave a Reply

%d bloggers like this: