List:General Discussion« Previous MessageNext Message »
From:John Nichel Date:March 13 2007 3:12pm
Subject:Weighting searches
View as plain text  
Hi list,

   I'm messing around with weighting my search results, and I've run 
into a weird quirk that I'm hoping someone here could help me 
understand.  I have a query like such:

SELECT
	xcart_products.product,
	xcart_product_meta.synonyms_misspellings,
	MATCH (xcart_product_meta.synonyms_misspellings)
	AGAINST ('couplers') AS rating
FROM
	xcart_products
LEFT JOIN
	xcart_product_meta
ON
	xcart_products.productid = xcart_product_meta.productid
WHERE
	MATCH (xcart_product_meta.synonyms_misspellings)
	AGAINST ('couplers')
GROUP BY
	product
ORDER BY
	rating DESC
LIMIT 2

The execution of this query returns the following:

[product] => Guinness Keg Coupler
[synonyms_misspellings] => couplers, couplers, coupler, guinness, 
guiness, Keg Coupler, beer Coupler, Coupler, keg tap,Guinness Stout, 
Harp Lager,  Kilkenny, Fits U System Keg Valves,
[rating] => 5.85046195983887

[product] => US Sankey Lever Handle Coupler
[synonyms_misspellings] => couplers, couplers, couplers, coupler, sankey 
coupler, coupler sankey, tap, taps, keg tap, keg tap, keg taps, US 
coupler, US coupler, sankey coupler, snadkey, coupler, sandkey coupler, 
sanky coupler, tap, beer tap, beer taps, sankey
[rating] => 5.02479267120361

The good thing is, it's returning a very relevant result set, but I'm 
not understanding how it's weighting this.  You'll notice that the last 
result set has a lower rating, but contains an exact match for the 
search term three times, where the higher rated item only contains it twice.

The db is MySQL 4.1.20 and the column synonyms_misspellings has a 
FULLTEXT index on it (the db is set to index on 3 characters).  Can 
anyone help me understand why it's not rating the one with the match 
three times higher than the one with the match twice, and point me in 
the direction of how to make it work that way?

-- 
John C. Nichel IV
Programmer/System Admin
Dot Com Holdings of Buffalo
716.856.9675
jnichel@stripped
Thread
Weighting searchesJohn Nichel13 Mar
  • Re: Weighting searchesNils Meyer14 Mar