How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?

by

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 id, keyword, content,
  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) DESC

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.

More Related Contents:

  1. How to allow fulltext searching with hyphens in the search query
  2. With MySQL, how can I generate a column containing the record index in a table?
  3. Is it possible to have function-based index in MySQL?
  4. Does MySQL index foreign key columns automatically?
  5. MySQL find_in_set with multiple search string
  6. What does the KEY keyword mean?
  7. MySQL match() against() – order by relevance and column?
  8. MySQL search and replace some text in a field
  9. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  10. mysql fulltext search failure
  11. What is cardinality in MySQL?
  12. MySQL varchar index length
  13. Search for string within text column in MySQL
  14. What’s the difference between using INDEX vs KEY in MySQL?
  15. Search all columns of a table using a single where condition with single keyword in mysql
  16. Mysql Improve Search Performance with wildcards (%%)
  17. DOs and DONTs for Indexes [closed]
  18. SQL search multiple values in same field
  19. Django MySQL full text search
  20. How to disable index in innodb
  21. Higher cardinality column first in an index when involving a range?
  22. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  23. MySQL 5.0 indexes – Unique vs Non Unique
  24. Accent insensitive search query in MySQL
  25. MySQL distinction between e and é (e acute) – UNIQUE index
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. MySQL – why not index every field?
  28. VARCHAR as foreign key/primary key in database good or bad?
  29. MySQL: how to index an “OR” clause
  30. Workaround in mysql for partial Index or filtered Index?

Leave a Comment