List view threshold issue on datetime indexed columns

Question

I'm working on a SharePoint Online and try to do some filters using REST API but I'm currently facing an issue on my list above 5000 items.

I need to expand multiple fields, all of them are indexed and everything is working fine until I try to filter on a Datetime that is also indexed

For example:

This is working fine:

/_api/web/lists/getByTitle('Entry')/items?$expand=Collaborator, User, Author&$select=* , Author/EMail, Author/Title,  Collaborator/Title, Collaborator/EMail, User/Title, User/EMail

But this won't:

/_api/web/lists/getByTitle('Entry')/items?$expand=Collaborator, User, Author&$select=* , Author/EMail, Author/Title,  Collaborator/Title, Collaborator/EMail, User/Title, User/EMail&$filter=EntryDate ge datetime'2022-07-16T22:00:00.723Z' and EntryDate le datetime'2022-10-17T21:59:59.723Z'

All columns used are properly indexed but I face the threshold issue and I cannot filter using those columns.

Answer 1

This is a known limitation in SharePoint. You cannot retrieve more than 5000 items if filtered query returns more than 5000 items, even after applying indexing on the column used in filtering.

In case you want to use multiple filter conditions, the first filter condition must be the strongest one and in order to apply remaining ones, it must return less than 5000 items.

---

For more information, check below links:

1. Recursive function to handle list view threshold not working
2. Filtering large list (more than 5K items) using two columns with REST API
3. List view threshold exceeded for unfiltered list retrieved by REST API