Dear MySQL developers:
This is probably a little surprising for you -- getting a question like this
from someone who is not a software developer. But, I saw that this list
looked at a 'weighted average' question back in July of last year, and
various other 'weighting' issues. Maybe people from this list can help me
with a 'raising relevance' question.
I'm a librarian working on a database of bibliographic information for
agriculture and water quality. [Sorry about any incorrect nomenclature, if
I start speaking librarian-ese.]
Our MySQL Db (offline) is the newest version of this searchable Web
directory (at this point -- under 2000 records with fewer than 30 fields
configured in one table):
http://www.nal.usda.gov/wqic/wqdb/esearch.html
Here is my problem: I want highly relevant results at the top of the
results list from a relevance search. Out on the Web, most of us expect
this from search engines. I'm certain it's not magic; but it may be
difficult to design into open source software.
Here is the scenario: Search terms contained in the Title column are the
most important. If both queried terms (below example: nutrient* and
management*) show up in Title, I want those rows to return high up in the
results. To me, this is a weighting question: As in applying a weighted
average to a set of fields prior to searching for data in those fields.
With a weighted average, someone might indicate that the Title field is the
more important of several fields in a search and change the average for that
field to 50%. The other fields could be adjusted as applicable. Retrieval
on specific terms would lift records with those terms in Title field to the
top of the results list. I hope to make this the default search for
first-time users of the database.
We haven't been able to find out how it works, or if it's possible, in
MySQL.
I've provided one SQL statement from a search page that may illustrate my
need. This is the statement created by our developer to run what we are
calling a 'best match' (relevance) search. I would like the Title column to
be the most heavily weighted.
SELECT *,MATCH
(Title,Subject_NALThesauru,Subject_ControlledV,Coverage_Jurisdicti)
AGAINST ('nutrient* management*' IN BOOLEAN MODE ) as Relevance FROM WQTest2
WHERE MATCH
(Title,Subject_NALThesauru,Subject_ControlledV,Coverage_Jurisdicti)
AGAINST('nutrient* management*' IN BOOLEAN MODE) having Relevance > 0.2
ORDER BY Relevance DESC LIMIT 0,20
Questions: Can 'relevance weighting' be changed for specific fields within
one MySQL table? Or, is a 'weighting command' executed through the SQL
statement? Specifically, how would we set up this search to produce
better-organized and more-relevant results?
I don't know the specifics of the hardware and other configuration factors,
but CAN find out. Just ask me what you need to know.
Any advice would be appreciated. Thanks very much.
Best regards,
--Stu Gagnon
==============================================
Stuart Gagnon, University of Maryland Cooperator Librarian
Water Quality Information Center, National Agricultural Library
10301 Baltimore Avenue, 130 West Wing
Beltsville, Maryland 20705-2351
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
