SharePoint Online: REST API for List, calling Filt

Question

I am trying to call REST API for list using below REST API

https://myweb.sharepoint.com/teams/sites/subwebs/_api/web/lists/GetByTitle('MyList')/Items?
$top=1
&$orderby=ID
&$select=ID,FName,LName,Title
&$filter=Title eq 'Female'

I need $filter should work with number of records limited to $top. The $top works if $filter is not applied.

Well, my list contains the items more than 5000. I received below error message while making GET Request for above URL

{
"readyState": 4,
"responseText": "{\"odata.error\":{\"code\":\"-2147024860, Microsoft.SharePoint.SPQueryThrottledException\",\"message\":{\"lang\":\"en-US\",\"value\":\"The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.\"}}}",
"responseJSON": {
    "odata.error": {
        "code": "-2147024860, Microsoft.SharePoint.SPQueryThrottledException",
        "message": {
            "lang": "en-US",
            "value": "The attempted operation is prohibited because it exceeds the list view threshold enforced by the `enter code here`administrator."
        }
    }
},
"status": 500,
"statusText": "Internal Server Error"
}

Answer 1

I know this may sound very obvious, but the first filter must return 5,000 items or less. Then you can add other filters. You may not need the $top parameter in this case. For instance:

https://site/_api/web/lists/GetByTitle('<List Title>')/Items?$filter=<Column Name> eq '<A value that you know returns 5,000 items or less>' and Title eq 'Female'

Answer 2

The exception Microsoft.SharePoint.SPQueryThrottledException is thrown since $filter=Title eq 'Female' query causes to walk through the whole list and check every row to see if it matches.

According to MSDN:

The list view threshold does not apply simply to the number of results returned by your query. Instead, it restricts the numbers of database rows that can be accessed in order to complete execution of the query at the row level in the content database.

That's why $top query option is not applicable here.

One option to avoid the issue would be indexing the Title field.

Go to List Settings -> Indexed Columns -> Create a new index -> select Title as a Primary Column:

Once Title field is indexed, the following query should succeed:

https://site/_api/web/lists/GetByTitle('<list title>')/Items?$top=1&$orderby=ID&$select=ID,Title&$filter=Title eq '<value>'