title: MySQL FULLTEXT Search Across >1 Table
body: <p>As a more general case of <a href="https://stackoverflow.com/questions/668210/mysql-syntax-and-or-performace">this question</a> because I think it may be of interest to more people...What's the best way to perform a fulltext search on two tables? Assume there are three tables, one for programs (with submitter_id) and one each for tags and descriptions with object_id: foreign keys referring to records in programs. We want the submitter_id of programs with certain text in their tags OR descriptions. We have to use MATCH AGAINST for reasons that I won't go into here. Don't get hung up on that aspect.</p>
  
  <pre><code>programs
    id
    submitter_id
  tags_programs
    object_id
    text
  descriptions_programs
    object_id
    text
  </code></pre>
  
  <p>The following works and executes in a 20ms or so:</p>
  
  <pre><code>SELECT p.submitter_id
  FROM programs p
  WHERE p.id IN
      (SELECT t.object_id
      FROM titles_programs t
      WHERE MATCH (t.text) AGAINST ('china')
  UNION ALL
      SELECT d.object_id
      FROM descriptions_programs d
      WHERE MATCH (d.text) AGAINST ('china'))
  </code></pre>
  
  <p>but I tried to rewrite this as a JOIN as follows and it runs for a very long time. I have to kill it after 60 seconds.</p>
  
  <pre><code>SELECT p.id 
  FROM descriptions_programs d, tags_programs t, programs p
  WHERE (d.object_id=p.id AND MATCH (d.text) AGAINST ('china'))
  OR    (t.object_id=p.id AND MATCH (t.text) AGAINST ('china'))
  </code></pre>
  
  <p>Just out of curiosity I replaced the OR with AND. That also runs in s few milliseconds, but it's not what I need. What's wrong with the above second query? I can live with the UNION and subselects, but I'd like to understand.</p>