Aggregate most relevant results with MySQL's fulltext search across many tables

Question

I am running fulltext queries on multiple tables on MySQL 5.5.22. The application uses innodb tables, so I have created a few MyISAM tables specifically for fulltext searches.

For example, some of my tables look like

account_search
===========
id
account_id
name
description
hobbies
interests

product_search
===========
id
product_id
name
type
description
reviews

As these tables are solely for fulltext search, they are denormalized. Data can come from multiple tables and are agregated into the search table. Besides the ID columns, the rest of the columns are assigned to 1 fulltext index.

To work around the "50%" rule with fulltext searches, I am using IN BOOLEAN MODE.

So for the above, I would run:

SELECT *, MATCH(name, type, description, reviews) AGAINST('john') as relevance
FROM product_search
WHERE MATCH(name, type, description, reviews) AGAINST('john*' IN BOOLEAN MODE) LIMIT 10

SELECT *, MATCH(name, description, hobbies, interests) AGAINST('john') as relevance
FROM account_search
WHERE MATCH(name, description, hobbies, interests) AGAINST('john*' IN BOOLEAN MODE) LIMIT 10

Let's just assume that we have products called "john" as well :P

The problem I am facing are:

To get meaningful relevance, I need to use a search without IN BOOLEAN MODE. This means that the search is subjected to the 50% rule and word length rules. So, quite often, if I most of the products in the product_search table is called john, their relevance would be returned as 0.
Relevances between multiple queries are not comparable. (I think a relevance of 14 from one query does not equal a relevance of 14 from another different query).
Searches will not be just limited to these 2 tables, there are other "object types", for example: "orders", "transactions", etc.

I would like to be able to return the top 7 most relevant results of ALL object types given a set of keywords (1 search box returns results for ALL objects).

Given the above, what are some algorithms or perhaps even better ideas for get the top 7?

I know I can use things like solr and elasticsearch, I have already tried them and am in the proces of integrating them into the application, but I would like to be able to provide search for those who only have access to MySQL.

F21 F21 · Accepted Answer · 2012-04-20T02:50:43

So after thinking about this for a while, I decided that the relevance ranking has to be done with 1 query within MySQL.

This is because:

Relevance between seperate queries can't be compared.
It's hard to combine the contents of multiple searches together in meaningful ways.

I have switched to using 1 index table dedicated to search. Entries are inserted, removed, and updates depending on inserts, removals and updates to the real underlying data in the innodb tables (this is all automatic).

The table looks like this:

search
==============
id //id for the entry
type //the table the data came from
column //column the data came from
type_id //id of the row the in the original table
content //text

There's a full text index on the content column. It is important to realize that not all columns from all tables will be indexed, only things that I deem to be useful in search has been added.

Thus, it's just a simple case of running a query to match on content, retrieve what we have and do further processing. To process the final result, a few more queries would be required to ask the parent table for the title of the search result and perhaps some other meta data, but this is a workable solution.

I don't think this approach will really scale (updates and inserts will need to update this table as well), but I think it is a pretty good way to provide decent application wide search for smaller deployments of the application.

For scalability, use something like elastic search, solr or lucene.

Aggregate most relevant results with MySQL's fulltext search across many tables

1 Answers