Filtered Views – More on Indexes:
When we create a view for a SharePoint List, its always advised to index the first column that is specified in the filter. Other columns may or may not be indexed as the view does not use those indexes. It always goes with the first column.
If the filter needs to be on two columns, then AND is always suggested rather than OR as OR filter increases the number of items returned and indexing will not help anymore.
1. Do not index look up column as it does not help in list view threshold. Instead use another data type column as primary or secondary index.
2. Check the recycle bin for deleted items. They are still residing in the database and it will also affect the returned list based on the filter used.
Let us see some examples which columns should be indexed based on our filter.
If we are filtering based on: New Items
Column to be indexed: Created
If we are filtering based on a specific column for eg.: Department
Column to be indexed: Department
3. Even if we created indexes based on the filters like above, certain additional operations might not be benefited by this as they still need to look into the entire library and perform the required operation.