Practical Query Limits and Indexing
When a filtered column is indexed, the practical query limit is not a fixed number of items, but rather a requirement that the result set of that specific filter must return fewer than 5,000 items to be rendered in a view. While indexing allows SharePoint to scan lists containing millions of items without triggering the threshold error, the index only facilitates the search; it does not allow a single view to display more than 5,000 items at once.
The 20-Index Limit and Long-Term Scaling
The limit of 20 indexed columns per list creates a ceiling for how many unique query patterns you can support. As lists grow beyond 20,000 items (and potentially into the millions), the 20-index limit affects scaling in the following ways:
- Query Rigidity: You cannot create an index for every possible filter combination. You must identify the most common access patterns and reserve indexes for those specific columns.
- Compound Filtering: If you filter by multiple columns, only the first filter applied must be on an indexed column to bring the result set below 5,000. Subsequent filters can be non-indexed, provided the initial indexed filter already reduced the count sufficiently.
- Architectural Shift: Once you exhaust your 20 indexes or find that filtered result sets consistently exceed 5,000 items, you must move away from standard List Views and adopt the Search API or Microsoft Graph API, which are designed for large-scale discovery rather than direct list querying.
Pagination vs. Indexing
Pagination and indexing serve different purposes and cannot be used interchangeably to bypass the threshold.
| Strategy |
Primary Purpose |
Threshold Interaction |
| Indexing |
Data Retrieval |
Allows the system to find a subset of data in a large list without scanning every item. |
| Pagination |
Data Presentation |
Controls how many items are shown per page, but the underlying query must still satisfy the threshold. |
Prefer Indexing when: You need to filter a large dataset to a specific, manageable subset (e.g., "Status = Active").
Prefer Pagination when: You have already successfully filtered the data to under 5,000 items and simply want to improve page load performance for the end user.
Verification Steps
To verify your current configuration, use the following path in the SharePoint UI:
- Navigate to List Settings.
- Under the Columns section, select Indexed columns.
- Confirm the number of existing indexes and ensure the column used in your view filter is listed here.
Note: If your list already exceeds 5,000 items, you may be unable to create new indexes through the UI. In this case, you must temporarily reduce the item count or use PowerShell to manage the index.
Diagnostic Detail Needed: Are you querying this data via the SharePoint Browser UI, or through a custom application using REST/Graph API?
microsoft-365