SharePoint 2010 Indexing Best Practices
Indexing, and it’s related searching/querying components is vital to integrate into the overall SharePoint design, and thus, there are some key points to consider. SharePoint offers its own indexing mechanism for working with the database table structure. The indexes can be created by SharePoint in the settings for the list. There are several things to consider with indexes. They are needed to help with filtering on lists for the list view threshold and there are both single and compound indexes that can be created. There is a limit of 20 indexes available. You need to reserve index slots for SharePoint features as you may need to create some columns later on. Two common ones are eDiscovery (SharePoint 2010 eDiscovery) and Information Management Policy Retention. Create single indexes for a single field to filter with the content query Web Parts and create compound indexes for queries that are going to filter two columns. They will return more items than the list view threshold that is singular. Indexes can reduce the overall performance of various operations.
You have a limit of 20 indexes so you have to make sure you can get all of your lists and stuff in there. The indexed columns are very important for a large list. It is important to select both your single and compound indexes very carefully. There are features to use for indexes too including the metadata navigation feature and the key filters. Create indexes that allow columns for filtering the navigation information architecture.
SharePoint 2010 allows you to create indexes automatically if you want. They do count for your limit of indexes. There are the SharePoint features that are very common for the document libraries and they should be included:
Hold & Record Status This offers in place records management & eDiscovery. The column is added and indexed when you have an in place records management size that collects or the eDiscovery feature enabled.
Expiration date This is part of the information management policy. The column is added along with content type and indexed.
Features of metadata navigation allow for the creation of various indexes within the hierarchy and key filter columns. They allow for the selection of the metadata navigation and the settings page. Since indexes are created for the hierarchy tree files. They are also created for certain key filter types so that the index results can be returned, and they can be used alone. With compound indexed they are created with various supports in place. The key filters allow for both the tree filter and the key filter to be used.
With a list that features many columns, you need to filter in order to successfully manage your indexes. You are forced to do so manually so that you don’t reach your index limit. When there are certain combinations of navigation in the hierarchies and key filters you won’t have to use them to create compound indexes. You also don’t have to reduce the number of indexes. When creating such indexes you need to choose single indexing for columns that are selective. They can be used alone in the list view or they can be filtered.
Compound indexes can be used when two filters are going to be used in metadata navigation. They can also be used for custom queries so that the index won’t have to be selective on its own. When you create indexes for columns they are used for filtering with list views and Web Parts. There are times when many indexes won’t be beneficial though. That is when compound filters come in handy. They offer you a method for creating an index that more efficiently uses the queries.
With those compound indexes you will be able to operate on two columns. The metadata navigation is enables and the fall back logic can be used when you configure them. The use of compound indexes shouldn’t be done by views though unless it is in conjunction with a metadata navigation query.
Indexes are a requirement when you perform queries on lists with more items than the list view threshold allows. This will increase the overall performance level. The indexes are required for performing effective queries on large lists. They can also cause other operations to be slower though. Items that have to be created, updated, or deleted in the indexes should be tested.
The metadata navigation defaults to creating both single and compound indexes. You can disable this setting though. When they are enabled a single index is created for each of the supported columns. The compound index will also offer support for the navigation of hierarchies and key filters. The compound indexes don’t allow for the combining of two key filters.
However, they do allow for you to manually create them. The metadata navigation helps to create most types of columns for support of the indexes. When the single indexes aren’t created for key filter types, there are fewer values that can’t be selected alone.
This information offers an inside look at the indexes which are created by metadata navigation automatically. This type of navigation offers a single value index for all of the columns supported. The metadata navigation feature allows users to choose a navigation hierarchy and a key filter. They are automatically used to create the compound indexes for all supported combinations.