My frontend category page isn't opening now after waiting almost 2 minutes getting memory exhausted we have tried to allocate 18GB memory but it gets stuck on this query and through error memory exhausted.
SELECT 1 AS
cat_indexON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id='4' INNER JOIN
stock_status_indexON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1 WHERE (stock_status_index.stock_status = 1)
Can anyone please suggest me how can I overcome this issue or speed up the processes and MySQL queries what architecture level changes should I do to make it work the category list page on frontend. I know this is huge data but it's our requirement to have this much products. We have a pretty good server
- Intel Xeon E5-2689v4
- 128 GB RAM
- 1200 GB Datastores