Post by ceebeeit » Wed May 31, 2023 3:07 am

Our provider says this and wants us to do something about it:

=== TOP 10 of 333 (total) Slow Queries for the past 24 hours =====================================================

1. Executed 6h 36m 26s ago for 19.200916 sec on Database --> dbdvfpgpk91znt

Date: 2023-05-26 12:07:06 Query_time: 19.200916 Rows_examined: 274171: Rows_sent 5 Lock_time: 0.000603 Query_chars: 1184

SELECT SQL_CALC_FOUND_ROWS p.product_id, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.produc

......

%' ) OR ( LCASE(p.model) LIKE '%the%' or LCASE(p.model) LIKE '%meeting%' or LCASE(p.model) LIKE '%on%' or LCASE(p.model) LIKE '%the%' or LCASE(p.model) LIKE '%t%' ) ) GROUP BY p.product_id ORDER BY p.sort_order ASC, LCASE(pd.name) ASC LIMIT 0,5;
=========================
We use Opencart 3.0.3.2 with about 65.000 products.
What can we do about this?

New member

Posts

Joined
Fri Feb 22, 2013 8:33 pm

Post by straightlight » Wed May 31, 2023 4:24 am

Wrong forum section. What it doesn't indicate on your first post is which hosting package you're using along with these stats reports.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by khnaz35 » Wed May 31, 2023 10:17 pm

The query in question is quite complex, involving multiple JOINs and subqueries, and it's also using the LIKE keyword, which can slow down queries significantly, especially when used with wildcard characters (%) on large tables.

There are few things can be done but its time consuming would suggest hire a developer and assign him/her task.

Urgent Questions shoot here: khnaz35@gmail.com
Enjoy nature ;) :) :-*


User avatar
Active Member

Posts

Joined
Mon Aug 27, 2018 11:30 pm
Location - Malaysia
Who is online

Users browsing this forum: Alexa [Bot] and 3 guests