Find Jobs
Hire Freelancers

OpenCart DB query slowing down the site sometimes

₹1500-12500 INR

Chiuso
Pubblicato più di 5 anni fa

₹1500-12500 INR

Pagato al completamento
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?
Rif. progetto: 18250021

Info sul progetto

5 proposte
Progetto a distanza
Attivo 5 anni fa

Hai voglia di guadagnare un po'?

I vantaggi delle offerte su Freelancer

Imposta il tuo budget e le scadenze
Fatti pagare per il lavoro svolto
Delinea la tua proposta
La registrazione e le offerte sui lavori sono gratuite
5 freelance hanno fatto un'offerta media di ₹5.342 INR
Avatar dell'utente
Hi, I am Opencart expert and speed optimzer. I will optimize that sql query so it will run fast everytime. Harsh
₹1.500 INR in 3 giorni
4,9 (627 valutazioni)
8,2
8,2
Avatar dell'utente
yes sure I can help you now.
₹11.111 INR in 2 giorni
4,9 (121 valutazioni)
6,3
6,3
Avatar dell'utente
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.
₹1.850 INR in 2 giorni
0,0 (0 valutazioni)
0,0
0,0

Info sul cliente

Bandiera: INDIA
Ahmedabad, India
0,0
0
Metodo di pagamento verificato
Membro dal nov 1, 2018

Verifica del cliente

Grazie! Ti abbiamo inviato tramite email il link per richiedere il tuo bonus gratuito.
Non è stato possibile inviarti l'email. Riprova per piacere.
di utenti registrati di lavori pubblicati
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
Caricamento anteprima
Autorizzazione per la geolocalizzazione concessa.
La tua sessione è scaduta ed è stato effettuato il log out. Accedi nuovamente per piacere.