title: Fulltext search with relevance - why the need for a multiple columns index?
body: <p>I have to implement fulltext search in multiple columns with result weighting based on relevance of certain columns / fields.</p>
  
  <p>All the solutions I've come across seem to use single-column indexes for calculating relevance and one multiple-column index for the <code>WHERE</code> clause. See: <a href="https://stackoverflow.com/a/600915/168719">https://stackoverflow.com/a/600915/168719</a> or <a href="https://stackoverflow.com/a/6305108/168719">https://stackoverflow.com/a/6305108/168719</a></p>
  
  <p>Here's my query then:</p>
  
  <pre><code>SELECT MATCH(name) AGAINST (text) as relevance_name, 
  MATCH(description) AGAINST(text) as relevance_description, 
  MATCH(description_long) AGAINST (text) as relevance_description_long 
  FROM products WHERE
  </code></pre>
  
  <hr>
  
  <p>And I'm facing the choice between:</p>
  
  <p><strong>a)</strong> </p>
  
  <pre><code>MATCH(name, description, description_long) AGAINST (text) &gt; 0
  </code></pre>
  
  <p><strong>b)</strong></p>
  
  <pre><code>MATCH(name) AGAINST (text) &gt; 0 
  OR MATCH(description) AGAINST (text) &gt; 0 
  OR MATCH(description_long) AGAINST (text) &gt; 0
  </code></pre>
  
  <hr>
  
  <p>After which the sorting clause comes.</p>
  
  <pre><code>ORDER BY (relevance_name * 2 + 
  relevance_description * 3 + 
  relevance_description_long * 4) / 9
  </code></pre>
  
  <p>The question is - what is the superiority of <strong>a</strong> (apparently the preferred method) over <strong>b</strong>? </p>
  
  <p><strong>a</strong> requires creating another fulltext index (across all searchable columns), which obviously takes more disk space. </p>
  
  <p>What are the advantages? Is it a matter of performance? Or search quality?</p>