Create three full text indexes
- a) one on the keyword column
- b) one on the content column
- c) one on both keyword and content column
Then, your query:
SELECT ... other non-full-text-cols,
MATCH (keyword) AGAINST ('watermelon') AS rel1,
MATCH (content) AGAINST ('watermelon') AS rel2,
FROM table
WHERE MATCH (keyword,content) AGAINST ('watermelon')
ORDER BY (rel1*1.5)+(rel2)
The point is that rel1
gives you the relevance of your query just in the keyword
column (because you created the index only on that column). rel2
does the same, but for the content
column. You can now add these two relevance scores together applying any weighting you like.
However, you aren't using either of these two indexes for the actual search. For that, you use your third index, which is on both columns.
The index on (keyword,content) controls your recall. Aka, what is returned.
The two separate indexes (one on keyword only, one on content only) control your relevance. And you can apply your own weighting criteria here.
Note that you can use any number of different indexes (or, vary the indexes and weightings you use at query time based on other factors perhaps ... only search on keyword if the query contains a stop word ... decrease the weighting bias for keywords if the query contains more than 3 words ... etc).
Each index does use up disk space, so more indexes, more disk. And in turn, higher memory footprint for mysql. Also, inserts will take longer, as you have more indexes to update.
You should benchmark performance (being careful to turn off the mysql query cache for benchmarking else your results will be skewed) for your situation. This isn't google grade efficient, but it is pretty easy and "out of the box" and it's almost certainly a lot lot better than your use of "like" in the queries.
I find it works really well.