MySQL Forums
Forum List  »  MyISAM

Position-weighted full text ranking
Posted by: Paul Hudson
Date: August 01, 2014 10:00AM


We're looking to run a MyISAM full-text query that ranks results based on word position in the query string. Example data might look like this:

- The cat sat on the mat
- The bat sat on the mat
- The cat sat on the bat
- The cat flew in the air

Our input would be something like, "bat foo sat bar mat baz", and what we're looking for is to run a full-text query that uses all those words and returns rows that match as many of them as possible, with preference given to words from the start.

For example, as there are six input words, we might assign "bat" a weight of six, "foo" a weight of five, "sat" a weight of four, then "bar" 3, "mat" 2 and "baz" 1, then sort rows by those ranks. We don't care where a word appears inside the data, only inside the query – i.e. if "bat" appears at the end of a field it's as important as if it appears at the beginning.

We've already looked into the > operator for binary mode full-text searches, but a) we're not sure whether it's possible to use it this way, and b) we're not sure it's sane! This would make the query look something like ">>>>>bat >>>>foo >>>sat >>bar >mat baz", and even reading that makes me cringe.

So: the question we have is: what's the smart way of doing what we're trying to do?

Thanks in advance!


Options: ReplyQuote

Written By
Position-weighted full text ranking
August 01, 2014 10:00AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.