I'm having a problem with the fulltext searching, and was looking for some
help.
i'm currently working with the following query:
select table.*
from table where match(title, description) against ('*search term*' IN
BOOLEAN MODE)
the reason I am using boolean mode, is so that it matches things like
"search terms", rather than excluding those results if i left off the
boolean mode.
my problem, is that the exact phrase "search term" shows up in the result
set, but is not ranked the highest since it's only a boolean search, its
just checking if it exits.
if I add: match(title,description) against ('search term') as score
to my select and order by score, it works fine, but my query time goes from
0.0623 to 3.7 seconds. which seems like a huge give in performance, which I
assume comes from ordering by a dynamically created field, so I tried to
fake out the query by changing the query to
select table.* from table where (match(title, description) against ('search
term') or match(title, description) against ('*search term*' in boolean
mode))
but the ordering is still off.
is there a way to have ordered by the non-boolean query, but still be able
to include those results?
seems bad that I can only have exact matches and proper ordering, or partial
matches and no ordering.
select video.id, video.title, video.description,
(match(title, description) against ('woot monkey')) as straight,
(match(title, description) against ('*woot monkey*' in boolean mode)) as
stars,
(match(title,description) against ('"woot monkey"' in boolean mode)) as
exact
from video where (match(title, description) against ('*woot monkey*' IN
BOOLEAN MODE)
and file_complete = 'true') order by exact DESC, stars DESC, straight DESC
