Slow query log

I'm using Opencart 1.5.1.3 and I'm getting a very slow response on the admin side. I currently have about 10,000 products and 50 categories. (specifically when I click on products link), sometimes just loading the admin section the first time will also get very slow.

My slow query log shows the following:

# Time: 120201 17:11:32
# Query_time: 38.077574 Lock_time: 0.000542 Rows_sent: 1 Rows_examined: 86676290
use restock_ocart;
SET timestamp=1328134292;
SELECT COUNT(DISTINCT p.product_id) AS total FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN vendors vds ON (vd.vendor = vds.vendor_id) WHERE pd.language_id = '1';

# Time: 120201 17:12:11
# Query_time: 39.269997 Lock_time: 0.000131 Rows_sent: 20 Rows_examined: 86685619
SET timestamp=1328134331;
SELECT * FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN vendor vd ON (pd.product_id = vd.vproduct_id) LEFT JOIN vendors vds ON (vd.vendor = vds.vendor_id) WHERE pd.language_id = '1' GROUP BY p.product_id ORDER BY pd.name ASC LIMIT 0,20;

Any advise?

BTW, the storefront is fine no slow downs at all....only until I click the product link in the admin....then the whole site stops until it finishes the process.

This is a result of a poor server to be honest. You need to use a better server with caching enabled on mysql queries to boost performance. To be fair, the SQL queries are hardly great, since they have a lot of joins in them, which requires more processing power

in addition both those queries are related to vendor which is not a default opencart functionality, so I would send an inquiry to the author of whatever mod you have installed that has to do with vendors.

JAY6390 wrote:This is a result of a poor server to be honest. You need to use a better server with caching enabled on mysql queries to boost performance. To be fair, the SQL queries are hardly great, since they have a lot of joins in them, which requires more processing power

I will enable a better caching on queries. Thanks JAY6390 for the input.

Xsecrets wrote:in addition both those queries are related to vendor which is not a default opencart functionality, so I would send an inquiry to the author of whatever mod you have installed that has to do with vendors.

Hi Xsecrets,

Thanks for looking at my issue. I will seek help from the module vendor.