Why 1250 items, if the limit is 5000 - SharePoint List Threshold

It was quite challenging and surprising as well for me that why SharePoint list is showing 1250 items only while the threshold limit it set to 5000 items. I found this link but it wasn't so much helpful to understand the reason. 

As per Microsoft on this  article, SharePoint list shows 1250 items in the following scenario. 

"If a user creates or loads a view that cannot use an index to query the list, then Metadata Navigation and Filtering will construct and execute a fallback query. A fallback query is a modified version of the original user query that displays a partial set of the items requested because it queries against only a portion of the list instead of the entire list. It is intended to provide you with some useful results in circumstances when the original query is blocked due to large list throttling. Up to 1,250 of the newest items are displayed based on when those list items were added to the list. Occasionally, fallback queries will return 0 results if no items in the part of the list scanned by the query contain results that match the original user query."

So what's the workaround? Here is the solution to show 5000 items in each custom view filter.

Solutions:

1. Increase the threshold limit for list or turn of throttling.

2. Create an Indexed column and create a filter on it

1.  Increase the threshold limit for list or turn of throttling.

Below scripts helps to disable throttling for a SharePoint list.

$web = Get-SPWeb http://url/to/web/with/list
$list = $web.Lists[“BIG_LIST_NAME”]
$list.EnableThrottling = $false

Warning:  Throttling is enabled for a good reason.  Disabling throttling can cause performance problems.  If you need to disable throttling for a specific list, I recommend moving the site collection containing the list to a dedicated content database.  This way if SQL table locking performance issues would be contained to the sites in that content database.

2. Create an Indexed column and create a filter on it.

Check this or this link to create an Indexed column. Only set that column to Indexed column on which you'll apply filter in List Custom View.

It may take time to complete the indexing operation. In my case it was 10000+ items and it took around 2 to 3 days to complete it. But after that i was able to filter 5000 items per view.

Now create a filtered view. Edit current view and change in filter field as per on this link.

Hope this help. Please write in comments if you have any query.

Regards