none
Add index to large list

    Question

  • Hey there,

    I have a customer list that has reached this limit of 5000 items, and now we need to add an index to facilitate filtered views to remain below the 5000 item limit.  Unfortunately it seems that once you reach the limit you can no longer add indexes to the list:

    "The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator"

    Since SharePoint Online doesn't let you temporarily disable the 5000 item limit, and there is no daily large query window - how do we rescue this data?

    Since you can't add indexes and hence create filtered views once the list goes past the 5000 item limit - I assume there still some way to make use of the data - i.e. it does not become irretrievable?

    To put it another way, I am assuming here that the story for customers is not: "Store items in SharePoint Online lists, up to 5000 items and then all your data is lost."

    Any suggestions apart from deleting all the data are welcome.

    Cheers,

       James.


    James Boman BSc. MCP:EAD

    Wednesday, February 22, 2017 10:47 AM

Answers

  • Sara,

        Thanks for taking the time to reply.  I have searched around and it seems that when lists get to 5001 items and you have not added indexes, the only solution is to delete your data.

    1. Create different views for the list and add the filter in the view to filter less than 5000 items.

    2. Organize items by folders. Create different folders for different categories.

    3. Split one list into different lists according to some rules.

    Just to let you know for next time why your suggestions are invalid:

    1. You can't create views on large lists that filter by columns that don't already have an index, and you can't add an index to a large list with more then 5000 items.
    2. You can only create folders in document libraries - not for normal lists.
    3. Obviously not exceeding 5000 item limit would also be a solution, but I specifically asked for any solution that did not involve deleting the data.

    I ended up writing a powershell script to copy the 78,000 items into a temporary list, remove all the items in the original list, add the index, and then copy all the items back.  Took O365 about 4 hours to process this in batches of 200 items.

    Now views CAN be created on the list to filter by columns as long as each view returns less than 5000 items at a time.

    Ironically the large list limit is supposed to be a precaution against database load, but it is actually has the reverse effect. 

    Cheers,

       James.


    James Boman BSc. MCP:EAD

    • Marked as answer by James Boman Friday, February 24, 2017 1:12 AM
    Friday, February 24, 2017 1:12 AM

All replies

  • Hi James,

    There are some workarounds to manage large lists in SharePoint online.

    1. Create different views for the list and add the filter in the view to filter less than 5000 items.

    2. Organize items by folders. Create different folders for different categories.

    3. Split one list into different lists according to some rules.

    More references:

    Manage large lists and libraries in Office 365.

    https://support.office.com/en-us/article/Manage-large-lists-and-libraries-in-Office-365-b4038448-ec0e-49b7-b853-679d3d8fb784?ui=en-US&rs=en-US&ad=US&fromAR=1

    Manage large lists and libraries in SharePoint.

    https://support.office.com/en-us/article/Manage-large-lists-and-libraries-in-SharePoint-b8588dae-9387-48c2-9248-c24122f07c59

    Best regards,

    Sara Fan


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Thursday, February 23, 2017 7:54 AM
    Moderator
  • Sara,

        Thanks for taking the time to reply.  I have searched around and it seems that when lists get to 5001 items and you have not added indexes, the only solution is to delete your data.

    1. Create different views for the list and add the filter in the view to filter less than 5000 items.

    2. Organize items by folders. Create different folders for different categories.

    3. Split one list into different lists according to some rules.

    Just to let you know for next time why your suggestions are invalid:

    1. You can't create views on large lists that filter by columns that don't already have an index, and you can't add an index to a large list with more then 5000 items.
    2. You can only create folders in document libraries - not for normal lists.
    3. Obviously not exceeding 5000 item limit would also be a solution, but I specifically asked for any solution that did not involve deleting the data.

    I ended up writing a powershell script to copy the 78,000 items into a temporary list, remove all the items in the original list, add the index, and then copy all the items back.  Took O365 about 4 hours to process this in batches of 200 items.

    Now views CAN be created on the list to filter by columns as long as each view returns less than 5000 items at a time.

    Ironically the large list limit is supposed to be a precaution against database load, but it is actually has the reverse effect. 

    Cheers,

       James.


    James Boman BSc. MCP:EAD

    • Marked as answer by James Boman Friday, February 24, 2017 1:12 AM
    Friday, February 24, 2017 1:12 AM
  • Hi James,

    It is very happy that you have resolved your problem.

    Thank you for your sharing and it will help others have the same issue.

    Best regards,

    Sara Fan

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com


    Friday, February 24, 2017 1:28 AM
    Moderator
  • Thank you for calling out the cookie-cutter answer. I wish you could down vote solutions that are applied as a one-size fix-all and demonstrate the poster did not even read your question.
    Tuesday, July 18, 2017 9:46 PM
  • "Cookie cutter" answers are what you get when you hire temp staff with zero knowledge to manage your so-called tech forums. At least this one didn't propose themselves as "the answer" like a lot of them do.

    Thanks for posting your solution James, looks like I need to break out the Powershell console to fix the same issue. This limit basically kills any chance of development work on any list >5000 items -get it right first time or else.

    • Edited by MrMellie Monday, October 16, 2017 12:55 PM
    Monday, October 16, 2017 12:52 PM