title: Optimizing fulltext search across multiple tables
body: <p>I want to search the requested term ($q) in my content table on the title &amp; the keywords but also for the models, which are in another table and linked by a table in between. Also, I need to get the number of views in another table.</p>
  
  <p>This is the query that I have been working on so far, the result is fine but it's way too slow (0.6s on average when I run it in PhpMyAdmin... We have millions of visitors per month)</p>
  
  <pre><code>SELECT DISTINCT SQL_CALC_FOUND_ROWS
      c.*,
      cv.views,
      (MATCH (c.title) AGAINST ('{$q}') * 3) Relevance1,
      MATCH (c.keywords) AGAINST ('{$q}') Relevance2,
      (MATCH (a.`name`) AGAINST ('{$q}') * 2) Relevance3
  FROM
      content AS c
  LEFT JOIN
      content_actors AS ca ON ca.content = c.record_num
  LEFT JOIN
      actors AS a ON a.record_num = cm.actor
  LEFT JOIN
      content_views AS cv ON cv.content = c.record_num
  WHERE
      c.enabled = 1
  GROUP BY c.title, c.length
  HAVING (Relevance1 + Relevance2 + Relevance3) &gt; 0
  ORDER BY (Relevance1 + Relevance2 + Relevance3) DESC
  </code></pre>
  
  <p>The tables architecture looks like this:</p>
  
  <pre><code>content
  record_num     title     keywords
  1              Video1    Comedy, Action, Supercool
  2              Video2    Comet
  
  content_actors
  content     model
  1           1
  1           2
  2           1
  
  actors
  record_num     name
  1              Jennifer Lopez
  2              Bruce Willis
  
  content_views
  content     views
  1           160
  2           312
  </code></pre>
  
  <p>Here are the indexes I found by doing SHOW INDEX FROM tablename:</p>
  
  <pre><code>Table              Column_Name     Seq_in_index     Key_name     Index_type
  ---------------------------------------------------------------------------
  content            record_num      1                PRIMARY      BTREE
  content            keywords        1                keywords     FULLTEXT
  content            keywords        2                title        FULLTEXT
  content            title           1                title        FULLTEXT
  content            description     1                description  FULLTEXT
  content            keywords        1                keywords_2   FULLTEXT
  
  content_actors     content         1                content      BTREE
  content_actors     actor           2                content      BTREE
  content_actor      actor           1                actor        BTREE
  
  actors             record_num      1                PRIMARY      BTREE
  actors             name            1                name         BTREE
  actors             name            1                name_2       FULLTEXT
  
  content_views      content         1                PRIMARY      BTREE
  content_views      views           1                views        BTREE
  </code></pre>
  
  <p>Here is the EXPLAIN of the query:</p>
  
  <pre><code>ID     SELECT_TYPE     TABLE     TYPE       POSSIBLE_KEYS          KEY         ROWS      EXTRA
  1      SIMPLE          c         ref        enabled_2, enabled     enabled     29210     Using where; Using temporary; Using filesort
  1      SIMPLE          ca        ref        content                content     1         Using index
  1      SIMPLE          a         eq_ref     PRIMARY                PRIMARY     1
  1      SIMPLE          cv        eq_ref     PRIMARY                PRIMARY     1
  </code></pre>
  
  <p>I am using the GROUP BY to avoid duplicate content, but this group by alone seems to double the time required to process the query.</p>
  
  <p><strong><em>EDIT</em></strong> Well after playing with the query a bit, I realized that if I remove the GROUP BY I get duplicates, if I let the GROUP BY there, it doesn't take the proper Relevance3 value (without the GROUP BY, one is returning a value for Relevance3 while the other is not...)</p>