title: Why do multiple WHERE conditions slow query rather than speed it up?
body: <p>The problem is that the query in question runs very slow when compared to the query run with one or two, rather than all three of its conditions.</p>
  
  <p>Now the query.</p>
  
  <pre><code>Select Count(*)
  From 
      SearchTable 
  Where 
      [Date] &gt;= '8/1/2009' 
      AND 
      [Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))
      AND 
      FreeText([Description], 'keyword list here')  
  </code></pre>
  
  <p>The first condition is self explanatory. The second uses a UDF to get a list of Zip Codes within 150 miles of 30348. The third uses a full text index to search for the provided words.</p>
  
  <p>With only this condition</p>
  
  <pre><code>[Date] &gt;= '8/1/2009' 
  </code></pre>
  
  <p>The query returns 43884 (table size is just under 500k rows) in <strong>3 seconds</strong>.</p>
  
  <p>Using only this condition </p>
  
  <pre><code>[Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))
  </code></pre>
  
  <p>I get 27920, also returned in <strong>3 seconds</strong>.</p>
  
  <p>And with only the full text portion</p>
  
  <pre><code>FreeText([Description], 'keyword list here')
  </code></pre>
  
  <p>68404 is returned in <strong>8 seconds</strong>.</p>
  
  <p>When I use just the zip code and full text conditions I get 4919 in <strong>4 seconds</strong>.<br>
  Just the date and full text conditions gets me 9481 in just shy of <strong>14 seconds</strong>.<br>
  Using the date and Zip Code conditions only gives me 3238 in <strong>14 seconds</strong>.<br>
  With all three conditions the query returns 723 in <strong>2 minutes, 53 seconds</strong>. <em>(wtfbbq)</em></p>