Full Text Scoring
I am attempting to improve the search functionality on my online photo gallery and I am hoping someone could help me answer a question. I have two tables with the following columns:
TABLE 1 (tbl_keywords):
keyword (this one is indexed)
TABLE 2 (tbl_imgname)
I am attempting to build a full-text search that will order the images based on the number of 'hits' each image gets based on the search term (in this example: "baby turtle"). Here is the query I've come up with:
select tbl_keyword.keyword, tbl_imgname.img_name,
match(tbl_keyword.keyword) against('baby turtle' in boolean mode) as score
left join tbl_imgname
where match(tbl_keyword.keyword) against('baby turtle' in boolean mode)
order by img_name asc
This produces the following result:
KEYWORD :: IMG_NAME :: SCORE
baby :: 0101010101010002 :: 1
baby :: 0302180000000051 :: 1
baby :: 0302180000000052 :: 1
turtle :: 0307191204420005 :: 1
baby :: 0307191204420005 :: 1
sea turtle :: 0307191204420005 :: 1
baby :: 0307191207490013 :: 1
turtle :: 0307191207490013 :: 1
My question is: Is there a way to add up the scores for each occurrence of an IMG_NAME (ie. 0307191204420005 would have a score of 3 considering it has three 'hits' on the keywords, 0307191207490013 would have 2, etc.) so I could order them by the total score at the end? Am I going about doing this the right way?
Please excuse me if there is a simple solution to this as I am still very much a newbie with mysql. Your help is GREATLY appreciated. Thanks!
Full Text Scoring
October 25, 2008 11:27PM
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.