<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns:MadCap="http://www.madcapsoftware.com/Schemas/MadCap.xsd" MadCap:lastBlockDepth="2" MadCap:lastHeight="120" MadCap:lastWidth="624" MadCap:disableMasterStylesheet="true" MadCap:tocPath="Basics Operations & Concepts|Indexing" MadCap:InPreviewMode="false" MadCap:RuntimeFileType="Topic" MadCap:TargetType="WebHelp" MadCap:PathToHelpSystem="../../../" MadCap:HelpSystemFileName="index.xml" MadCap:SearchType="Stem"> <head><title>When And Where</title> <script type="text/javascript">/* <![CDATA[ */ window.onload = function(){ var pathToFlash = $('html').attr('MadCap:PathToHelpSystem') + 'Content/Resources/Code/ZeroClipboard.swf'; ZeroClipboard.setMoviePath(pathToFlash); function bindToClipBord(element,content){ var clip = new ZeroClipboard.Client(); clip.setText(content); clip.glue(element); }; if(location.protocol==='file:'){ $('.copylink-marker').remove(); } else{ $('.copylink-marker').each(function(){ var text = $(this).parent().parent().children('.prettyprint').html(); $(this).hover(function(){ bindToClipBord(this,text); }, function(){}); }); } prettyPrint(); }; /* ]]> */</script> <link href="../../SkinSupport/MadCap.css" rel="stylesheet" /> <link href="../../Resources/Stylesheets/OnlineStyle.css" rel="stylesheet" /> <script src="../../Resources/Code/prettify.js"> </script> <script src="../../Resources/Code/lang-vb.js"> </script> <script src="../../Resources/Code/jquery.min.js"> </script> <script src="../../Resources/Code/ZeroClipboard.js"> </script> <script src="../../SkinSupport/MadCapAll.js" type="text/javascript"> </script> </head> <body> <p class="MCWebHelpFramesetLink" style="display: none;"><a href="../../../index_CSH.html#basics/indexing/when_and_where.htm" style="">Open topic with navigation</a> </p> <div class="MCBreadcrumbsBox"><span class="MCBreadcrumbsPrefix">You are here: </span><a class="MCBreadcrumbsLink" href="../../basics.htm">Basics Operations & Concepts</a><span class="MCBreadcrumbsDivider"> > </span><a class="MCBreadcrumbsLink" href="../indexing.htm">Indexing</a><span class="MCBreadcrumbsDivider"> > </span><span class="MCBreadcrumbs">When And Where</span> </div> <p> <script type="text/javascript">/*<![CDATA[*/document.write('<a href="' + location.href +'">'); document.write("Direct Link"); document.write('</a>');/*]]>*/</script> </p> <p> </p> <h1>When And Where</h1> <p>When do you need an index? And on which fields do I need an index? An index has a costs and benefits. <a href="costs_and_benefits.htm" target="" title="" alt="" class="MCXref" xrefformat="See "{paratext}"">See "Costs And Benefits"</a>. Therefore you should only add one when you actually benefit from it.</p> <h2>When To Add Queries</h2> <p>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.</p> <p>Also add index only on fields which are used in queries. Indexes on fields which are never used in a query have no benefit.</p> <h2>Where To Add Queries</h2> <p>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 <a href="../../configuration/common/diagnostics.htm">diagnostic-API</a> 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.</p> <div class="codesnippet" MadCap:conditions="Primary.c#"> <pre class="prettyprint" xml:space="preserve">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); } } }</pre> <div class="codesnippet-meta">WhereToIndexExample.cs: Index diagnostics <div class="codedownload"><a href="../../CodeExamples/indexing/where/Example-indexing-where-csharp.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div> </div> <div class="codesnippet" MadCap:conditions="Primary.VB.NET"> <pre class="prettyprint lang-vb" MadCap:conditions="Primary.Online" xml:space="preserve">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</pre> <div class="codesnippet-meta">WhereToIndexExample.vb: Index diagnostics <div class="codedownload"><a href="../../CodeExamples/indexing/where/Example-indexing-where-vb.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div> </div> <div class="codesnippet" MadCap:conditions="Primary.c#"> <pre class="prettyprint" xml:space="preserve">configuration.Common.Diagnostic.AddListener(new IndexDiagnostics());</pre> <div class="codesnippet-meta">WhereToIndexExample.cs: Find queries which cannot use index <div class="codedownload"><a href="../../CodeExamples/indexing/where/Example-indexing-where-csharp.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div> </div> <div class="codesnippet" MadCap:conditions="Primary.VB.NET"> <pre class="prettyprint lang-vb" MadCap:conditions="Primary.Online" xml:space="preserve">configuration.Common.Diagnostic.AddListener(New IndexDiagnostics())</pre> <div class="codesnippet-meta">WhereToIndexExample.vb: Find queries which cannot use index <div class="codedownload"><a href="../../CodeExamples/indexing/where/Example-indexing-where-vb.zip" class="codedownload" MadCap:conditions="Primary.Online">Download Code</a></div><div class="codedownload copylink-marker" MadCap:conditions="Primary.Online"><a href="#copy">Copy Code</a></div></div> </div> <script type="text/javascript" src="../../SkinSupport/MadCapBodyEnd.js"> </script> </body> </html>