title: How to implement a MySQL fulltext search across multiple tables?
body: <p>I’m trying to do a full-text search in three seperate tables and sort the results by relevancy.</p>
  <p>During my searches for the answer, I found out that I can’t use fulltext search in multiple tables. So I added a separate fulltext index for each column I want to search.</p>
  <p>Now the problem is that I can do the search but I can’t do sorting as I would like to.</p>
  <p>Here’s my tables:</p>
  <pre><code>CREATE TABLE books (
   bookID int(11) NOT NULL AUTO_INCREMENT,
   title varchar(300) NOT NULL,
   authorID int(11) NOT NULL,
   FULLTEXT KEY title (title)
  )
  
  CREATE TABLE IF NOT EXISTS authors (
   authorID int(11) NOT NULL AUTO_INCREMENT,
   authorNamevarchar(200) NOT NULL,
   FULLTEXT KEY authorName(authorName)
  );
  
  CREATE TABLE IF NOT EXISTS chapters (
   chapterID int(11) NOT NULL AUTO_INCREMENT,
   bookID int(11) NOT NULL,
   content longtext NOT NULL,
   FULLTEXT KEY content (content)
  );
  </code></pre>
  <p>And here is my MySQL query where I’m stuck:</p>
  <pre><code>SELECT *, 
   MATCH(books.title) AGAINST('$q') as tscore,
   MATCH(authors.authorName) AGAINST('$q') as ascore
   MATCH(chapters.content) AGAINST('$q') as cscore
  FROM books 
  LEFT JOIN authors ON books.authorID = authors.authorID 
  LEFT JOIN chapters ON books.bookID = chapters.bookID 
  WHERE 
   MATCH(books.title) AGAINST('$q')
   OR MATCH(authors.authorName) AGAINST('$q')
   OR MATCH(chapters.content) AGAINST('$q')
  ORDER BY ???? DESC
  </code></pre>
  <p>Now with this query I can do sorting by titles, authors or contents. What I want to do is, get the relevancy for all the three columns together and order the results by that.</p>
  <p>And, yes I’m aware of other search engines like Lucene or Sphinx, but I’m not planning to use them now.</p>