0

l've a problem with filter.

I've 3 custom filter ( pietre , materiali , inspiration ); When select Category , Pietre and Materiali , query that populates search_tmp finish at least in 1 minute.

Query processes 54403700 records

INSERT INTO `avm_search_tmp_5f171481ecd303_40667492` SELECT `main_select`.`entity_id`, SUM(score) AS `relevance` FROM (SELECT DISTINCT `search_index`.`entity_id`, (((0) + (0) + (0) + (0)) * 1) AS `score` FROM `avm_catalog_product_index_eav` AS `search_index` INNER JOIN `avm_catalog_product_index_eav` AS `materiale_filter` ON `search_index`.`entity_id` = `materiale_filter`.`entity_id` AND `materiale_filter`.`attribute_id` = 152 AND `materiale_filter`.`store_id` = 6 INNER JOIN `avm_catalog_product_index_eav` AS `pietre_filter` ON `search_index`.`entity_id` = `pietre_filter`.`entity_id` AND `pietre_filter`.`attribute_id` = 150 AND `pietre_filter`.`store_id` = 6 INNER JOIN `avm_cataloginventory_stock_status` AS `stock_index` ON stock_index.product_id = search_index.entity_id AND `stock_index`.`website_id` = 0 AND `stock_index`.`stock_id` = 1 INNER JOIN `avm_cataloginventory_stock_status` AS `sub_products_stock_index` ON sub_products_stock_index.product_id = search_index.source_id AND `sub_products_stock_index`.`website_id` = 0 AND `sub_products_stock_index`.`stock_id` = 1 INNER JOIN `avm_catalog_product_index_eav` AS `visibility_filter` ON search_index.entity_id = visibility_filter.entity_id AND `visibility_filter`.`attribute_id` = 99 AND `visibility_filter`.`value` in ('2', '4') AND `visibility_filter`.`store_id` = '6' INNER JOIN `avm_catalog_category_product_index_store6` AS `category_ids_index` ON search_index.entity_id = category_ids_index.product_id AND category_ids_index.store_id = '6' WHERE (search_index.store_id = '6') AND (category_ids_index.category_id = 5) AND (materiale_filter.value = '6168') AND (pietre_filter.value = '5770')) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC, `entity_id` DESC LIMIT 10000

I need to remove:

  • INNER JOIN avm_cataloginventory_stock_status AS stock_index
  • INNER JOIN avm_cataloginventory_stock_status AS sub_products_stock_index

Where can I do this modify?

Or other solutions for to decrease the proccess time.

Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.