How to implement a MySQL fulltext search across multiple tables?

You should be able to add the tscore, ascore, and cscore values in the ORDER BY clause.

Try this:

SELECT *,   MATCH(books.title) AGAINST('$q') as tscore,  MATCH(authors.authorName) AGAINST('$q') as ascore,  MATCH(chapters.content) AGAINST('$q') as cscoreFROM 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 (tscore + ascore + cscore) DESC

@Ike Walker's solution is great, however in my case I wanted to roll up the one-to-many results into a single row per search result. Riffing on @Ike Walker's solution here's how I got the job done:

Schema:

T1: ArticlesT2: Comments (many comments to one article)

Indexes:

ALTER TABLE articles ADD FULLTEXT title_index (title)ALTER TABLE articles ADD FULLTEXT body_index (body)ALTER TABLE comments ADD FULLTEXT comment_index (comment)

SQL:

SELECT     articles.title,     SUM(MATCH(articles.title) AGAINST('$q') +     MATCH(articles.body) AGAINST('$q') +     MATCH(comments.comment) AGAINST('$q')) as relevance FROM     articles LEFT JOIN     comments ON articles.id = comments.article_id WHERE     MATCH(articles.title) AGAINST('$q')     OR MATCH(articles.body) AGAINST('$q')     OR MATCH(comments.comment) AGAINST('$q') GROUP BY     articles.id ORDER BY     relevance DESC

Note: If you want to add weights to each field you could do something like.

SUM((MATCH(articles.title) AGAINST('$q')*3) +         (MATCH(articles.body) AGAINST('$q')*2) +         MATCH(comments.comment) AGAINST('$q')) as relevance 

In this case title would have 3x, body 2x the value of a match in comments.

It depends on what you want to sort by. You could sort by author, then title, then chapter content with this

ORDER BY MATCH(authors.authorName) DESC ,MATCH(books.title) DESC ,MATCH(chapters.content) DESC

the idea being that when you find the authors name, it's more relevant than when it's found in the title, which in turn is more relevant than finding it in the chapter text. You could also sort by the total relevancy with

ORDER BY MATCH(authors.authorName) + MATCH(books.title) + MATCH(chapters.content) DESC

but that might give odd results, as something where the search text only shows up in the chapter content could show up before the title.