Search Team Wrote:
-------------------------------------------------------
> So in the ORDER BY clause, instead of:
>
>
> + (CASE WHEN MATCH(`category_4`) AGAINST
> ST ('+paper' IN BOOLEAN MODE)
> OR MATCH(`category_3`) AGAINST
> ory_3`) AGAINST ('+paper' IN BOOLEAN MODE)
> OR MATCH(`category_2`) AGAINST
> ory_2`) AGAINST ('+paper' IN BOOLEAN MODE)
> OR MATCH(`category_1`) AGAINST
> ory_1`) AGAINST ('+paper' IN BOOLEAN MODE)
> OR MATCH(`category_4`) AGAINST
> ory_4`) AGAINST ('+papers' IN BOOLEAN MODE)
> OR MATCH(`category_3`) AGAINST
> ory_3`) AGAINST ('+papers' IN BOOLEAN MODE)
> OR MATCH(`category_2`) AGAINST
> ory_2`) AGAINST ('+papers' IN BOOLEAN MODE)
> OR MATCH(`category_1`) AGAINST
> ory_1`) AGAINST ('+papers' IN BOOLEAN MODE) THEN 4
> ELSE 0 END)
>
>
> you would recommend:
>
>
> + (CASE WHEN
> EN
> MATCH(`category_4`,`category_3`,`category_2`,`category_1`)
> AGAINST ('+paper +papers' IN
> +paper +papers' IN BOOLEAN MODE)
> THEN 4 ELSE 0 END)
>
>
> ?
Yes, but you will need to remove plus signs. Otherwise, both "paper" and "papers" need to be present.
> This is a search function for an e-commerce site,
> so when a shopper enters a search string,
> I believe they are trying to have matches against
> the more important data fields come up
> higher in the search results than matches against
> the less important data fields.
Yes, I see that now.
>
> Could "NATURAL LANGUAGE MODE" achieve this? (I
> have never used FULLTEXT indicies myself)
I do not think so. It basically rates documents based on the ratio of frequency of search terms in the document and the frequency in the table as a whole.
However, you may be able to use the score for natural language mode for this part:
+ (CASE WHEN
IF(`pinfo`.`vendor` = 2
,MATCH(`desc_long`) AGAINST('"copy paper"' IN BOOLEAN MODE)
,MATCH(`desc_title`) AGAINST('"copy paper"' IN BOOLEAN MODE)
)
THEN 2 ELSE 0 END)
+ (CASE WHEN
IF(`pinfo`.`vendor` = 2
,MATCH(`desc_long`) AGAINST(+'copy +paper' IN BOOLEAN MODE)
,MATCH(`desc_title`) AGAINST('+copy +paper' IN BOOLEAN MODE)
)
THEN 1 ELSE 0 END)
A single MATCH statement like
MATCH(desc_long, desc_title) AGAINST ('copy paper') IN NATURAL LANGUAGE MODE
should score rows with both words higher than words with only one of them. Maybe that is all that is needed.
>
>
> I will also send a link to your response to the
> programmer.
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway