Page 1 of 1

$50-$75 for mysql query

Posted: Thu Jul 07, 2005 5:34 pm
by voltrader
The Problem:
- fulltext search does not present relevant results when ordering by [rating]; I explain why at the bottom of this post after the details.

- [rating] is an average of many ratings and is stored as a floating point number from 0 to 9

Info:

I have the following fields in table_1:

[name]
[category]
[address]
[city]
[state]
[info]
[rating]


I'm using fulltext search to generate results from two variables: keyword_1 and keyword_2.

- keyword_1 is used to match against [name],[category] and [info]
- keyword_2 is used to match against [address],[city] and [state]
- why is there another match against for keyword_2? Because I would like to increase the weighting for a positive match against [city] in addition to the above
- what is $orderby_modifier? Modifies "ORDER BY" to either sort by rating first or relevance first

Code: Select all

SELECT *,
MATCH ('table_1.name, table_1.category, table_1.info') AGAINST ('keyword_1') +
MATCH ('table_1.address, table_1.city, table_1.state') AGAINST ('keyword_2') AS key_1_plus_key_2_score,
MATCH ('table_1.city') AGAINST ('keyword_2') AS key_2_city_score
WHERE
MATCH ('table_1.name, table_1.category, table_1.info') AGAINST ('keyword_1') AND
MATCH ('table_1.address, table_1.city, table_1.state') AGAINST ('keyword_2') AND
MATCH ('table_1.city') AGAINST ('keyword_2')

$orderby_modifer
The default $orderby_modifier is:

Code: Select all

ORDER BY key_1_plus_key_2__score DESC, rating DESC
To order by rating:

Code: Select all

ORDER BY rating DESC, key_1_plus_key_2 DESC
Symptoms of the problem

The database has thousands of records. If user searches for keyword_1="something", keyword_2="somewhere", the script returns thousands of results, starting with the highest score down to the lowest. That's fine.

However, if the user wants to sort relevant results by [rating], irrelevant results sometimes appear at the top. For example, if keyword_1="nothing",keyword_2="nowhere" had the highest rating, even though it has a small total score, it'll be ordered first.

I would like to ORDER BY rating for only relevant results, if the sample is large. I assume (although I could be wrong), that the solution would entail comparing the MAX and MIN scores, then stripping out x% of the MIN scores if (MAX_score-MIN_score)>n .

I've stared at this too long... wondering if someone else is up to trying. In all probability I'm looking at this the wrong way.

I'll pay $50-75 depending on the complexity of the solution. If it works, I'll paypal you the funds.

Let me know if I need to clarify any points.

Thanks

Posted: Fri Jul 08, 2005 12:08 pm
by voltrader
Bump -- made request clearer

Posted: Fri Jul 08, 2005 2:25 pm
by timvw
http://dev.mysql.com/doc/mysql/en/fulltext-boolean.html

You could give score and rating a different "weight".

Code: Select all

SELECT *, 
  ((1.3 * (MATCH(title) AGAINST ('+term +term2' IN BOOLEAN MODE))) + 
  (0.6 * (MATCH(text) AGAINST ('+term +term2' IN BOOLEAN MODE)))) AS relevance FROM їtable_name] 
WHERE 
  (MATCH(title,text) AGAINST ('+term +term2' IN BOOLEAN MODE)) 
HAVING relevance > 0 
ORDER BY relevance DESC;

Posted: Fri Jul 08, 2005 2:29 pm
by voltrader
Thanks. I'll give this a try tim.

Posted: Fri Jul 08, 2005 2:57 pm
by timvw
Np, i just started to think about it.... Meaby a combination of rating * relevance would be better...

Posted: Fri Jul 08, 2005 3:10 pm
by voltrader
IC. So I ought to ORDER BY the product of rating and relevance. Sounds logical,and introducing a boolean search looks good. I'll give it a try.

Thanks again, and will keep you updated.