Mysql Gurus:
The individual SELECTS below seem to produce relevant results, but when I JOIN them with a UNION, the second SELECT seems to be ignored or assigned a very low priority (if that's possible).
The idea is that I have two search boxes -- one for name, the other for location. If a user searches for one or the other, the individual SELECTS work fine, however if they search for both, I JOIN the statements, and unfortunately get irrelevant results.
Can anyone shed any light on this?
search_index is the fulltext index created from search_detail.PHP Code:(SELECT search_detail.*,
MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST
('$keywords') AS score
FROM search_index, search_detail WHERE MATCH (search_index.name,search_index.type,search_index.cat_1) AGAINST ('$keywords')
AND (search_index.ref_number=search_detail.ref_number))
UNION
(SELECT search_detail.*,
MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword') AS score
FROM search_index, search_detail
WHERE MATCH (search_index.address_1,search_index.address_2,search_index.city,search_index.state,search_index.zip,search_index.country) AGAINST ('$xtra_keyword')
AND (search_index.ref_number=search_detail.ref_number) ORDER BY score)
Bookmarks