OpenCart DB query slowing down the site sometimes

Hi, The site ([login to view URL]) is built on Opencart 3 (Journal 3 theme + AWS Lamp T2 medium). Site's working fine mostly, but sometimes the server CPU utilization shoots up to 200% (2 cores) because of one mysql query. On setting up a slow_query log and testing with few orders, there's this particular query which takes the most time. SELECT c.category_id, c.parent_id, [login to view URL] as title , ( SELECT COUNT(p.product_id) FROM oc_product_to_category p2c LEFT JOIN oc_product p ON (p.product_id = p2c.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE [login to view URL] = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = c.category_id) as total FROM oc_category c LEFT JOIN oc_category_description cd ON (c.category_id = cd.category_id) LEFT JOIN oc_category_to_store c2s ON (c.category_id = c2s.category_id) WHERE cd.language_id = '1' AND c2s.store_id = '0' AND [login to view URL] = '1' ORDER BY c.sort_order, LCASE([login to view URL]); I think this is a generic query which outputs the table of category IDs { cat_id, parent_id, name, product count } . When run on localhost, please find the output of the query in File 1. PFA the action records which we tested in columns A-F in excel file. Columns I & J have the execution time and timestamp of the slow_query_log. PFA the dump of slow_query_db log too. PFA Cloudwatch CPU utlization graph too. I want to know the rootcause behind why this query takes 1) When and why is this query getting called? 2) Why is average time to execute high? ( it processes 84 lakh rows) 3) What is the alternative way of getting this done quickly without affecting site performance and utility? 4) Why is this query sometimes taking too long? Why is it VARYING sometimes and the other times the site is working smoothly?