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.

Maciemaciel

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

Assimilative

Very nice, what I needed for my project! – Inculpable

you can give weights to each field in the ordering by multiplying them Eg: ORDER BY (tscore*3 + ascore*2 + cscore) DESC – Craft

What is the dollar sign '$' used for? – Ahola

Combining relevance scores from different fulltext indices can produce unexpected results. Relevance scores from different indices are essentially incommensurable, since they are computed from factors such as number of tokens in the index, which can vary greatly. See mariadb.com/kb/en/full-text-index-overview/#relevance for details. – Wheel

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.

Rupe

You got the upvotes. One is mine ! Your code is a wonderful working example. The original post is almost 11 years old !!! But the query still counts in 2021 ! – Aflcio

Answer 3

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.

Indue

does not work i've tried it....you can do ORDER BY MATCH(..) AGAINST() which does the same thing as the second query – Murrell