Heute trat der „Fehler“ wieder auf das Suggest bracuht eeewig bis ein Suchergebnis angezeigt wird.
Hab mir mal das komplette SQL geloggt, vielleicht kann mir jemand sagen warum das Statement soo lange braucht.
Erst wenn ich die Datenbank lösche und neu importiere läuft die Suche wieder performant.
SELECT SQL_CALC_FOUND_ROWS product.id as __product_id, variant.id as __variant_id, variant.ordernumber as __variant_ordernumber, searchTable.* FROM s_articles product INNER JOIN s_articles_details variant ON variant.id = product.main_detail_id
AND variant.active = 1
AND product.active = 1 LEFT JOIN s_articles_avoid_customergroups avoidCustomerGroup ON avoidCustomerGroup.articleID = product.id
AND avoidCustomerGroup.customerGroupId IN (1) INNER JOIN (SELECT a.id as product_id, (sr.relevance
+ IF(a.topseller = 1, 50, 0)
+ IF(a.datum >= DATE_SUB(NOW(),INTERVAL 7 DAY), 25, 0)) as ranking FROM (SELECT srd.articleID, SUM(srd.relevance) as relevance, COUNT(DISTINCT term) as termCount FROM (
SELECT MAX(sf.relevance * sm.relevance) as relevance, sm.keywordID, term, si.elementID as articleID FROM (SELECT 100 as relevance, ‚dichtung‘ as term, 662 as keywordID
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 3176 as keywordID
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 111186 as keywordID
…
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 2390 as keywordID
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 107078 as keywordID
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 2386 as keywordID
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 108174 as keywordID
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 2387 as keywordID) sm INNER JOIN s_search_index si ON sm.keywordID = si.keywordID INNER JOIN s_search_fields sf ON si.fieldID = sf.id AND sf.relevance != 0 AND sf.tableID = 1 GROUP BY articleID, sm.term, sf.id
UNION ALL
SELECT MAX(sf.relevance * sm.relevance) as relevance, sm.keywordID, term, st2.articleID as articleID FROM (SELECT 100 as relevance, ‚dichtung‘ as term, 662 as keywordID
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 108174 as keywordID
UNION ALL SELECT 50 as relevance, ‚dichtung‘ as term, 2387 as keywordID) sm INNER JOIN s_search_index si ON sm.keywordID = si.keywordID INNER JOIN s_search_fields sf ON si.fieldID = sf.id AND sf.relevance != 0 AND sf.tableID = 5 GROUP BY articleID, sm.term, sf.id) srd GROUP BY srd.articleID ORDER BY relevance DESC LIMIT 5000) sr INNER JOIN s_articles a ON a.id = sr.articleID)) searchTable ON searchTable.product_id = product.id INNER JOIN s_articles_categories_ro productCategory ON productCategory.articleID = product.id
AND productCategory.categoryID IN (3) INNER JOIN s_articles_attributes productAttribute ON productAttribute.articledetailsID = variant.id WHERE avoidCustomerGroup.articleID IS NULL GROUP BY product.id ORDER BY searchTable.ranking DESC, variant.id ASC LIMIT 6