MySQL Forums
Forum List  »  Optimizer & Parser

Re: Can anyone help speed up this query?
Posted by: Øystein Grøvlen
Date: June 23, 2017 12:53AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Can anyone help speed up this query?
240
June 23, 2017 12:53AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.