title: MySQL Joins and Full Text search
body: <p>Any full-text, MySQL experts out there?</p>
  
  <p>I wrote a question yesterday asking for any suggestions on how to use full-text search with a lot of joins, a bit lazily, I hadn't actually made an attempt.</p>
  
  <p>Since then, I've a had a good attempt at mastering it myself, building a few successful test queries, against my full-text fields. If I do NOT use any joins, and query each field individually, the full-text functions work perfectly and relevance/boolean works great with great performance too... but... as soon as I add my joins to run the full query, it runs forever and ever.</p>
  
  <p>Can anybody spot anything in my query that might cause this to happen, because as an amateur, I really can't see it!</p>
  
  <pre><code>SELECT
  
  photos.photoID,
  photos.headline,
  photos.dateCreated,
  MATCH (people.people) AGAINST ('+sarah +harding' IN BOOLEAN MODE) AS Rel1
  
  FROM photos
  
  LEFT JOIN ( photoPeople INNER JOIN people ON photoPeople.peopleID = people.PeopleID ) 
  ON photos.photoID = photoPeople.photoID AND MATCH (people.people) AGAINST ('+sarah +harding' IN BOOLEAN MODE)
  
  WHERE
  
  photos.photoStatus = 'Live'
  
  GROUP BY
  
  photos.photoID
  
  ORDER BY Rel1
  </code></pre>
  
  <p>This is one of my successful, individual, queries:</p>
  
  <pre><code>SELECT
  
  photoID,
  headline,
  dateCreated,
  MATCH (caption) AGAINST ('+sarah +harding' IN BOOLEAN MODE) AS Relevance
  
  FROM photos
  
  WHERE
  
  photoStatus = 'Live'
  
  AND
  
  MATCH (caption) AGAINST ('+sarah +harding' IN BOOLEAN MODE)
  
  ORDER BY Relevance
  </code></pre>
  
  <p>This is the DB schema:</p>
  
  <pre><code>photos (tbl)
  photoID             INT(11)         Primary     Auto-Increment
  headline            Long-Text
  caption             Long-Text / FULLTEXT
  dateCreated         DateTime
  
  people (tbl)
  peopleID            INT(11)         Primary     Auto-Increment
  people              VarChar(255) / FULLTEXT
  
  photoPeople (tbl)
  photoID             INT(11)
  peopleID            INT(11)
  
  keywords (tbl)
  keywordID           INT(11)         Primary     Auto-Increment
  keyword             VarChar(255) / FULLTEXT
  
  photoKeyword (tbl)
  photoID             INT(11)
  keywordID           INT(11)
  
  photoContributor (tbl)
  photoID             INT(11)
  contributorRef      VarChar(100) / FULLTEXT
  </code></pre>
  
  <p>And this is my EXPLAIN print out:</p>
  
  <pre>
  id  select_type         table               type        possible_keys       key     key_len     ref     rows
  1   SIMPLE              photos              ALL         NULL                NULL        NULL        NULL        89830
  1   SIMPLE              photoContributor    ALL         NULL                NULL        NULL        NULL        149635  
  1   SIMPLE              photoPeople         ALL         NULL                NULL        NULL        NULL        110606
  1   SIMPLE              people              eq_ref      PRIMARY             PRIMARY     4       1   
  1   SIMPLE              photoKeyword        ALL         NULL                NULL        NULL        NULL        699102  
  1   SIMPLE              keywords            eq_ref      PRIMARY             PRIMARY     4       1
  </pre>
  
  <p>My website visitor should be able to search for:
  "Brad Pitt Angelina Jolie Dorchester Hotel Sunglasses @MG" - This should find "Brad Pitt" and "Angelina Jolie" from the 'people.people' table and possibly the 'photos.caption' table too. It should also find "Dorchester Hotel" from the 'photos.caption' table, "Sunglasses" from the 'keywords.keyword' table and lastly, it should find "@MG" in 'photoContributor.contributorRef' table.</p>
  
  <p>Any help on this would be gratefully received...</p>