When do you need an index? And on which fields do I need an index? An index has a costs and benefits. See "Costs And Benefits". Therefore you should only add one when you actually benefit from it.
Indexes speed up queries but slow down store and delete operations. You only need to add indexes when queries are too slow. There's no real benefit to add a index when your queries are already fast enough.
Also add index only on fields which are used in queries. Indexes on fields which are never used in a query have no benefit.
How do I find the queries which can benefit from indexes? How do I find queries which couldn't utilize indexes? You can use the diagnostic-API find out. Filter for the LoadedFromClassIndex-message. Every time this message arrives the query didn't use a field index. You can add a break-point to the message-output and find out which query is the source and then add the index.
internal class IndexDiagnostics : IDiagnosticListener { public void OnDiagnostic(IDiagnostic diagnostic) { if (diagnostic is LoadedFromClassIndex) { Console.WriteLine("This query couldn't use field indexes " + ((LoadedFromClassIndex) diagnostic).Reason()); Console.WriteLine(diagnostic); } } }
Friend Class IndexDiagnostics Implements IDiagnosticListener Public Sub OnDiagnostic(ByVal diagnostic As IDiagnostic) _ Implements IDiagnosticListener.OnDiagnostic If TypeOf diagnostic Is LoadedFromClassIndex Then Console.WriteLine("This query couldn't use field indexes " & DirectCast(diagnostic, LoadedFromClassIndex).Reason()) Console.WriteLine(diagnostic) End If End Sub End Class
configuration.Common.Diagnostic.AddListener(new IndexDiagnostics());
configuration.Common.Diagnostic.AddListener(New IndexDiagnostics())