I'm working on a search interface in MySQL for a series of items that
have titles and descriptions. I'd like to weight the results so that
matches in the title count for more than matches in the description.
Is there a recommended way of doing this?
So far, I've come up with three ways, two of which seem to work.
The first and most straightforward is to create a "keywords" table,
and then list the word multiple times depending on where it was listed
to give it extra weight. For example, if the title is "Children and
Dogs" and the description is "Boys and girls take their pets for
walks", I might put in:
children children dogs dogs boys girls take their pets walks
to give the words that appeared in the title, "children" and "dogs",
twice the weight of the other words.
The second is to list a column multiple times when creating the
index. For example:
create fulltext index test
on TxKeywords
(Title,Title,Description);
seems to have roughly the desired effect, but I don't exactly
understand what it's doing, and I don't know if this is supported
behavior, or if might go away in a future version.
The third is to create multiple fulltext indices, and multiply the
MATCH...AGAINST number against a multiplier. For example:
select *,
match(Title) against('children') * 2 +
match(Description) against ('children')
as score
from
TxKeywords
order by
(score)
limit 10
This is extremely slow---on a few thousand rows, it takes several
minutes at least, which just won't work for an interactive
application.
Has anybody done anything like this, and would they be willing to
share their experiences? Am I missing something obvious? Any way to
speed up the third method (which seems to be the most flexible)? Can
I count on the second method being supported in future releases?
Thanks for any tips,
-----ScottG.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
