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?
Hi,
I have seen the query you have sent and noticed that the complex quries like this are slowing your system.
See as per my knowledge here every application should take easy step on database server whereas application server can be extendes but databse servers have limits.
Simple queries should be there to get your server a good loading time.
I can modify the query time and further debug your system to get its loding time issue resolved.
Please reply to have a chat in detail.
Yes, for the matter of dealing with opencart i have worked for 1 year on it and devloped 4 sites up till now on opencart. I have also used the theme you mentioned, so i know about it also.