How Indexed Columns Improve SharePoint List Speed

In this article, we are going to understand indexed columns in SharePoint and see how they work behind the scenes.

Indexing column is like arranging your column data or items in a way that it can be easily found. In sharepoint lists, the threshold limit is 5000. Which means 5000 data or items can be queried at a time. So, indexing helps to avoid pitfalls while we are dealing with large amounts of data for filtering.

List View Threshold

Before we go deep down, let us understand what the List View Threshold is first.

There is no data storage limit, like you can not add more than 5000 data points or items. You can, but there is a limit associated with filtering or querying that data.

It means if you try to get more than 5000 items without using an indexed column, it throws a Threshold error like: “The attempted operation is prohibited because it exceeds the list view threshold…”

How do Indexed Columns Help?

When column(s) are indexed, Sharepoint uses that column with a filter or query and finds that exact item according to that filter or query. In short, think of it as a shortcut to find items from thousands of entries faster. Without indexing, SharePoint has to go through each and every item to get that item, which affects performance.

With indexed columns, sharepoint directly jumps to the item that we need, similar to a book index, where we can go to the page quickly.

Now let's talk about how it actually works with indexing or without indexing.

Suppose that you have 40,000 items stored in a SharePoint list.

Without indexing

• You filter columns to Status = “Approved”
• Sharepoint goes to each and every item to match that query.
• It checks all 40,000 items and becomes slow, and gives a threshold error.

With indexing

• Now you filter the query like Status = “Approved”
• SharePoint has already created a “Sorted table (like shortcut table)” for the status column.
• Hence, it is an indexed column, and it will directly jump to that item that matches this query.
• It makes it faster and fetches only those items that match the query.

Easy to Understand

Suppose that you are reading the book and you want to reach a particular chapter in your book, so if you have no index in the book then you will flip each and every page to find that chapter but if you have index you directly get the page number of that chapter and jump in to that. This is the simple analogy that is used for indexed columns.

What actually indexed columns can’t do.

• It does not increase storage to store data.
• It does not allow for storing more than 5000 data points in a view - it just helps to filter larger lists.
• Also, there are some column types that can not be indexed.

Now, let's take a look at how to create an indexed column.

1. Got to your list.
2. Click or gear( ⚙️) icon and click on list settings.
3. In the list setting page, you will see that the Indexed column option is just below the list of all columns, like in the image below.
   
4. Now, as you click on that, it will redirect to the page to add an indexed column like in the image below, click on the " Create a new index option.
5. It will give an interface in the image below.
   

In the above image, as per your requirement, select the primary column as well as a secondary column.

After selecting the desired column for indexing, click on Create, which will create that selected column as an indexed column. And then you will be able to see that indexed column like in the image below.

Now, in this way, we can create a maximum of 20 indexed columns in the SharePoint list. Which is the way to improve performance while dealing with larger data sets?

Conclusion

Indexed columns in SharePoint are like shortcuts to fetch filtered or queried data from larger data sets, lists, or libraries. They work by creating a behind-the-scenes index table, which helps to fetch data based on the items index and makes it faster than SharePoint, which loops over each item. So, indexed columns allow SharePoint to sort, filter, and query data without looping through all items.

Note. Use indexed columns only on the fields on which you are frequently sorting, filtering, and querying. (Indexed columns limit 20 per list).