Filtering large list (more than 5K items) using two columns with REST API

Question

I have a list which has more than 5K items in it and I need to filter it by using multiple columns. Below is the challenge that I am facing.

Scenario 1: Below REST API fails with Microsoft.SharePoint.SPQueryThrottledException because Group column has more than 5K items with value as Group5K.

/_api/web/lists/getbytitle('List5K')/items?$filter=Group eq 'Group5K'

Scenario 2: Below REST API works as SubGroup column has less than 5K items with value as SubGroup1

/_api/web/lists/getbytitle('List5K')/items?$filter=SubGroup eq 'SubGroup1'

Scenario 3: Below REST API works as combination of filter conditions from scenario 1 and 2 have less than 5K items.

/_api/web/lists/getbytitle('List5K')/items?$filter=SubGroup eq 'SubGroup1' and Group eq 'Group5K'

Scenario 4: If I change the sequence of filter conditions from scenario 3, below REST API fails with with Microsoft.SharePoint.SPQueryThrottledException

/_api/web/lists/getbytitle('List5K')/items?$filter=Group eq 'Group5K' and SubGroup eq 'SubGroup1'

It seems to me, in scenario 4, SharePoint is applying the first filter condition and then on the result it applies the second condition. Is this default behavior of SharePoint? Is there any workaround for this?

I may have a case where SubGroup column condition may also start returning more than 5K items but the combination of Group and SubGroup column will return less than 5K items. In that scenario changing the sequence will also fail.

How can I query large list with multiple columns?

P.S. Group and SubGroup columns are indexed and the list is on SharePoint Online.

Answer 1

Unfortunately, as far as I know, there are no workarounds for the issue. The first condition must be the strongest one and in order to apply remaining ones, it must return less than 5k items. For instance, you can filter by Created field in the first condition and then apply other conditions to the resulting subset containing less than 5k items. Another option is organizing items in folders, with less than 5k items in each, and request items from those folders:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest#retrieve-items-from-the-specified-folder

The folders here will work as additional indices. But if you need to retrieve items from multiple folders, you will have to make multiple requests.