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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
