i’ve a web based retailer with 6000~ merchandise, most of them configurable merchandise.
I’ve a number of layered navigation attributes so the client can simply discover what’s in search of, however when a number of of this filters are chosen the queries on database take a number of minutes to carry out.
We have now flat classes / merchandise enabled and in addition fpc.
Magento model is 2.3.1
Under is one instance of the queries
choose material_idx1
.worth
, group_concat(material_idx1.entity_id separator ',') as merchandise
from catalog_product_flat_5
as e
internal be part of catalog_category_product_index_store5
as cat_index
on cat_index.product_id=e.entity_id and cat_index.store_id=5 and cat_index.visibility in(2, 4) and cat_index.category_id=5
internal be part of catalog_product_index_price
as price_index
on price_index.entity_id = e.entity_id and price_index.website_id = '1' and price_index.customer_group_id = 0
internal be part of cataloginventory_stock_status
as stock_status_index
on e.entity_id = stock_status_index.product_id
internal be part of search_tmp_5ebed478c7d4f6_50708335
as search_result
on e.entity_id = search_result.entity_id
internal be part of catalog_product_index_eav
as thread_counts_idx
on thread_counts_idx.entity_id = e.entity_id and thread_counts_idx.attribute_id = 205 and thread_counts_idx.store_id = '5' and thread_counts_idx.worth in ('4')
internal be part of catalog_product_index_eav
as fabric_type_idx
on fabric_type_idx.entity_id = e.entity_id and fabric_type_idx.attribute_id = 206 and fabric_type_idx.store_id = '5' and fabric_type_idx.worth in ('8', '9')
internal be part of catalog_product_index_eav
as material_idx1
on material_idx1.entity_id = e.entity_id and material_idx1.attribute_id = '209' and material_idx1.store_id = '5' group by material_idx1
.worth
Can somebody please assist me ?
Regards