By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,271 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,271 IT Pros & Developers. It's quick & easy.

MySQL 4.0.1 fulltext search relevancy problem

P: n/a
I have a rather complicated query with a combination of LEFT JOINs and
two MATCHES where the first match is non-boolean to get the accurate
score, the second to search as boolean:

SELECT
image.id, image.image_name,
(MATCH (image_name, image_alt, image_location_city,
image_location_state, image_location_country) AGAINST ('test')
OR MATCH (first_name, last_name) AGAINST ('test')
OR MATCH (keyword_name) AGAINST ('test')
OR MATCH (event_name) AGAINST ('test')
OR MATCH (placement_name) AGAINST ('test')
) as score,
image.image_path, image.image_creation_date
FROM image

LEFT JOIN image_person_assoc ON image_person_assoc.image_id = image.id
LEFT JOIN person ON person.id = image_person_assoc.person_id
LEFT JOIN image_keyword_assoc ON image_keyword_assoc.image_id =
image.id
LEFT JOIN keyword ON keyword.id = image_keyword_assoc.keyword_id
LEFT JOIN image_event_assoc ON image_event_assoc.image_id = image.id
LEFT JOIN event ON event.id = image_event_assoc.event_id
LEFT JOIN image_placement_assoc ON image_placement_assoc.image_id =
image.id
LEFT JOIN placement ON placement.id =
image_placement_assoc.placement_id

WHERE MATCH (image_name, image_alt, image_location_city,
image_location_state, image_location_country) AGAINST ('+test+' IN
BOOLEAN MODE)
OR MATCH (first_name, last_name) AGAINST ('+test+' IN BOOLEAN MODE)
OR MATCH (keyword_name) AGAINST ('+test+' IN BOOLEAN MODE)
OR MATCH (event_name) AGAINST ('+test+' IN BOOLEAN MODE)
OR MATCH (placement_name) AGAINST ('+test+' IN BOOLEAN MODE)

GROUP BY image.id
ORDER BY score DESC, upper(image.image_name) ASC
Sample Results:
+-----+----------------------------+-------+-------------------------------------------------------------------------+---------------------+
| id | image_name | score | image_path
| image_creation_date |
+-----+----------------------------+-------+-------------------------------------------------------------------------+---------------------+
| 100 |blah.jpg | 1 | /html/images/blah.jpg |
2003-01-01 |
| 101 | mysql-81x42.png | 1 | /html/images/mysql-81x42.png
| 0000-00-00 |
+-----+----------------------------+-------+-------------------------------------------------------------------------+---------------------+
Using this query I always get a relevancy score of 1 every time; I do
not actually get the floating-point decimal number that I was seeking
(the accurate relevancy); this based on information I found at
http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html . The SQL query
is correct, though (I receive no SQl-related nor MySQL-related
errors), just not numerically accurate in its relevancy.

Anything I might need to do to finetune this?

Thanx
Phil
Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.