SharePoint list 5000 item threshold with indexed column confusion

Question

Today I have encountered the 5000 limit error even though my view is filtered on an indexed column. I believed that because the filtered view was on an indexed column then the result could return more than 5000 items?

So even if columns are indexed and used in a filtered view then it can only return a maximum of 5000 items?

This also affected other views that use the indexed column to filter and do not return anywhere near 5000 items. These views do sort on columns that are not indexed, would this have an impact?

Answer 1

At any point the filter should always return less than 5000 (resource limit) items.

Filtering as well as Sorting should be on Indexed Columns.

Answer 2

You will always encounter 5,000 Item limit, does not matter whether you indexed the columns or not. The difference though is that if you never indexed columns, your list will stop displaying entries once limit is exceeded.

However, if indexed columns and are filtering on those indexed columns, you will have an ability to display the list rows. As the previous responder stated, the secret here is to limit the # of entries per view. You will never be able to display more than 5000 entries per view and there should be no reason for this. Try to limit to 5o or 100-200 per view. That should be more than enough for a user to get to info they need with proper views/filtering, etc. Just like Google Search results, nobody every checks out Pages 5 or 6 of results. I have written a detailed post on the topic, you can check it here

Answer 3

You're correct, you can't sort a list with more than 5000 items on a non-indexed column, your views that use that sort will not work. You can try indexing those columns and that should fix your views.

However, I've seen multiple cases where those internal indexes went out of sync with the list data when lists had more than 5000 items and items were constantly deleted and added. In those cases the views stopped working even though all indexed fields were configured correctly. Microsoft support acknowledged the problem but said they couldn’t help when the issue involved actual data and had nothing to do with the list schema. As a workaround, we had to recreate the indexed fields and copy values over and the views would start working again.