title: Challenge! Complex MySQL Query
body: <p>We are coding a small search engine. Database Tables:</p>
  
  <pre><code>Documents (DocumentID, Title, Abstract, Author, ...)
  InvertedIndex (DocumentID, Word, Count)
  Stopwords (Word)
  </code></pre>
  
  <p>Where InvertedIndex has an entry for each word in each Document and the number of times it appears. Stopwords is simply a list of words that I dont care about. The engine is queried with lists of terms separated by or. For example:</p>
  
  <ul>
  <li>term1 term2</li>
  <li>term1 or term2</li>
  <li>term1 term2 or term3</li>
  </ul>
  
  <p>...etc. Search results based on Relevance, calculated for each document with the boolean extended model. and-ed terms (all terms that are not ored) are multiplied and ors are summed. For example, considering the query term1 term2 or term3, if the terms appear in a document 3, 4, and 5 times respectively, the document relevance would be (3*4)+5 = 12. Also, ignore terms that exist in Stopwords.</p>
  
  <p>OK Now... my professor has told us that calculating the relevance for all documents can be done in a single query. That's what I need help on.</p>
  
  <p>I've prepared some pseudocode for the example query <em>term1 term2 or term3</em>. So this is how I would calculate the relevance for every document, but I would like to perform a single MySQL query instead. I include this just as clarification for the relevance formula.</p>
  
  <pre><code>foreach document
      relevance = 0
      foreach term_set // where (term1 term2) would be a term_set and (term3) would be the other
          product = 1
          foreach term
              if term not in stopwords
                  SELECT Count FROM InvertedIndex WHERE Word=term AND DocumentID=document
                  product *= Count
          relevance += product
  </code></pre>
  
  <p><em>(EXP(SUM(LOG(COALESCE(Column,1)))</em> apparently is a way to perform <a href="http://lists.mysql.com/mysql/166184" rel="nofollow">aggregate multiplication</a>.</p>
  
  <p>Any help would be greatly appreciated. Sorry if this was a chore to get through. It's 2 oclock and I probably didnt explain this very well.</p>