MySQL Forums
Forum List  »  Optimizer & Parser

Re: Can anyone help speed up this query?
Posted by: Search Team
Date: June 22, 2017 09:56AM

Quote

> I think you can write
>
> MATCH(column1, column2) AGAINST ('term1 term2' IN BOOLEAN MODE)


So in the ORDER BY clause, instead of:

  + (CASE WHEN MATCH(`category_4`) AGAINST ('+paper' IN BOOLEAN MODE) 
               OR MATCH(`category_3`) AGAINST ('+paper' IN BOOLEAN MODE) 
               OR MATCH(`category_2`) AGAINST ('+paper' IN BOOLEAN MODE) 
               OR MATCH(`category_1`) AGAINST ('+paper' IN BOOLEAN MODE) 
               OR MATCH(`category_4`) AGAINST ('+papers' IN BOOLEAN MODE) 
               OR MATCH(`category_3`) AGAINST ('+papers' IN BOOLEAN MODE) 
               OR MATCH(`category_2`) AGAINST ('+papers' IN BOOLEAN MODE) 
               OR MATCH(`category_1`) AGAINST ('+papers' IN BOOLEAN MODE) THEN 4 ELSE 0 END)

you would recommend:

  + (CASE WHEN MATCH(`category_4`,`category_3`,`category_2`,`category_1`) 
                  AGAINST ('+paper +papers' IN BOOLEAN MODE)
          THEN 4 ELSE 0 END)

?



Quote

> It seems you are implementing your own ranking scheme. Have you considered whether the ranking
> scheme provided by NATURAL LANGUAGE MODE would be sufficient for your needs?


I am actually not the programmer who wrote this. It was passed along to me to optimize.

But yes, I believe that is exactly what they are trying to do.


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.

Could "NATURAL LANGUAGE MODE" achieve this? (I have never used FULLTEXT indicies myself)


I will also send a link to your response to the programmer.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Can anyone help speed up this query?
599
June 22, 2017 09:56AM


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.