OpenCart Community

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?

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

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.