Slow SQL Query on MATCH sorting by Relevance

Question

I have the following Query

SELECT
    product.AID,
    product.ART_ID,
    product.EAN,
    productdetails.DESCRIPTION_SHORT,
    MAX( 
        (100000 * (MATCH(productdetails.DESCRIPTION_SHORT) AGAINST ('"psen in1p"' IN BOOLEAN MODE)))+
        (100000 * (MATCH(product.ART_ID) AGAINST ('"psen in1p"' IN BOOLEAN MODE)))+
        (100000 * (MATCH(product.EAN) AGAINST ('"psen in1p"' IN BOOLEAN MODE)))+
        (100000 * (MATCH(product.SUPPLIER_ALT_PID) AGAINST ('"psen in1p"' IN BOOLEAN MODE)))+
        (10 * (MATCH(productdetails.DESCRIPTION_LONG) AGAINST ('*psen in1p*' IN BOOLEAN MODE)))+
        (2 * (MATCH(productdetails.KEYWORD) AGAINST ('+psen +in1p' IN BOOLEAN MODE)))

    ) AS relevance

FROM 
    tbl_product as product 
INNER JOIN 
    `tbl_product_details` as productdetails ON product.AID = productdetails.AID 
WHERE MATCH 
    (product.ART_ID,
     product.EAN,
     product.SUPPLIER_ALT_PID,
     product.ERP_GROUP_SUPPLIER) AGAINST ('*psen* *in1p*' IN BOOLEAN MODE)
OR MATCH 
    (productdetails.DESCRIPTION_SHORT,
     productdetails.DESCRIPTION_LONG,
     productdetails.MANUFACTURER_TYPE_DESC,
     productdetails.KEYWORD) AGAINST ('*psen* *in1p*' IN BOOLEAN MODE)
GROUP BY 
    product.AID 
ORDER BY
    relevance DESC

My Problem is, that the Query takes about ~3 Second which is ways to much. If i run the Statement without ORDER BY it takes about 0,0096 Seconds which is perfect. I dont know why it takes so long. I already tried to Subselect and Order the Subselect with the Same Result (About 3 Seconds to Finish. Same goes for a Subselect without ORDER BY.

The Database have about 600k Records and over 1 Million Records in tbl_product_details.

I'm thankfull for any Help on this Problem.

Explain for the Query with Order By (3 Seconds)

id  select_type  table           type   possible_keys                                                                                                                                                               key                              key_len                      ref     rows   Extra
1   SIMPLE       product         index  PRIMARY,tbl_product_catalog_id_foreign,tbl_product_supplier_id_foreign,tbl_product_art_id_index,tbl_product_ean_index,SUPPLIER_ALT_PID,ART_ID_2,ft_artid,ft_ean,ft_sapid    PRIMARY                          4  NULL                      569643  Using  temporary; Using filesort
1   SIMPLE       productdetails  ref    tbl_product_details_aid_foreign                                                                                                                                             tbl_product_details_aid_foreign  5  shop_meyle1.product.AID   1       Using  where

Explain for the Query without Order By (0,01 Seconds)

id  select_type  table           type   possible_keys                                           key                              key_len    ref                      rows    Extra  
1   SIMPLE       product         index  PRIMARY,tbl_product_catalog_id_foreign,tbl_product...   PRIMARY                          4          NULL                     569643  NULL
1   SIMPLE       productdetails  ref    tbl_product_details_aid_foreign                         tbl_product_details_aid_foreign  5          shop_meyle1.product.AID  1       Using where

Explain for the Query without Order By and with Subselect (3 Seconds)

id  select_type  table           type   possible_keys                                           key                              key_len    ref                      rows    Extra  
1   PRIMARY      <derived2>      ALL    NULL                                                    NULL                             NULL       NULL                     569643  NULL
2   DERIVED      product         index  PRIMARY,tbl_product_catalog_id_foreign,tbl_product...   PRIMARY                          4          NULL                     569643  NULL
2   DERIVED      productdetails  ref    tbl_product_details_aid_foreign                         tbl_product_details_aid_foreign  5          shop_meyle1.product.AID  1       Using where

Yeah, `'*psen* *in1p*'` give me exactly the Results im looking for, i changed the Search Term to yours the only difference here is that the relevance Value is more accurate. The Probleme still exists, if i want to sort my Results by Relevance, the Query takes 300x as long as without Order By — user3668976, Nov 30 '17 at 12:43
Well, just wanted to warn you that `match against('*psen*')` doesn't (in contrast to `like`) find e.g. `pppsend`, just `Psenpp`. If that is what you expect, it's fine. To your problem: ordering takes some time, but it alonoe should not take 300 times longer - which is why I suspected a `limit` somewhere, which would explain it. If your unordered query really gives all rows, just unordered, (so no limit e.g. in the workbench settings, pagination, ...) in 0.01s, then insert that into a temporary table and order it from there. How many rows do your queries return? — Solarflare, Nov 30 '17 at 16:24

Slow SQL Query on MATCH sorting by Relevance

0 Answers0