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
