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