How to implement a MySQL fulltext search across multiple tables?

Question

I’m trying to do a full-text search in three seperate tables and sort the results by relevancy.

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.

Now the problem is that I can do the search but I can’t do sorting as I would like to.

Here’s my tables:

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) ); 

And here is my MySQL query where I’m stuck:

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 

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.

And, yes I’m aware of other search engines like Lucene or Sphinx, but I’m not planning to use them now.

Answer 1

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 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 (tscore + ascore + cscore) DESC 

Answer 2

@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: Articles T2: 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.