Our research into indexing SharePoint columns indicates that the performance gains are more theoretical than actual. Generally, indexing works best with an unstructured multiple data source like a document, then something like an Account Name or ID field which by definition is already unique.
In other words, it makes sense to crawl and index a document library where each word is indexed and then the query references the page location where that word exists but a unique field like Account Name would always be indexed one to one.
In SQL Server, all column data goes into one table. Create an Account and fill in ten columns, ten rows get added to this table. Create a Contact or Opportunity and fill in eight columns, eight rows get added. There’s no “Accounts” table, no “Contacts” table, etc. – all one table – and that’s not just for one site but for the entire site collection. (The presumption is that when SharePoint needs the data for a particular list item then an SQL view will hand that information back.)
SharePoint indexing is not the same as with SQL Server indexing. It’s basically another layer on top of things and more layers generally equate to decreased performance and this has never been truer when it comes to SP indexing which is why we minimize indexing in our own systems.
There are benefits to SharePoint indexing, but we are not convinced that it translates very well to a relational system such as Flex. SharePoint doesn’t know anything about our relationships – we enforce the relationships ourselves through code. Traditionally people have used SharePoint to store, say, Contacts and then create views to pull them up. In that case, indexing is easy – you have a view for Contacts where you sort by the “Modified” column (you want to see which Contacts you’ve dealt with recently) then you’d index the “Modified” column. I think it really comes down to raw performance – you want 100,000 Accounts and you want speedy you are going to have to invest in some fast equipment and an excess of RAM.
If one wanted to test indexing in their environment they may wish to look at the columns being searched in the Quick Search and index those. A display page’s sub-grids could possibly benefit from CompanyID /OwnerModule /OwnerID being indexed. However, indices have both pros and cons based on the site setup and farm configuration. This is why we leave it for the client to experiment based on their situation and usage.
One can add indices for SharePoint lists. What this does in the background is lower the number of items affected during an operation which hopefully will put that operation under the threshold.
The main intention of indexing is to enhance item retrieval speed – however, they will generally cause additions/updates/deletions to be slightly slower. They will also increase the underlying content database size.
The biggest issue we’ve seen with SharePoint indexing is that in some situations we’ve had queries inexplicably fail to return any results and we solved this by removing an index – this would indicate that under some circumstances SharePoint may have (or had) a bug with indexing.
Be aware that indexing is not a bullet-proof method of avoiding the threshold limit. If the number of indexed items in an operation also exceeds the threshold limit then it will still cause an error.