MySQL Forums
Forum List  »  Full-Text Search

Full Text Scoring
Posted by: Lazaro Ruda
Date: October 25, 2008 11:27PM

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):
id_keyword
keyword (this one is indexed)

TABLE 2 (tbl_imgname)
img_name
id_keyword

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
from tbl_keyword
left join tbl_imgname
using (id_keyword)
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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Full Text Scoring
4173
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.