OpenCart Community

I was having some issues with a slow DB and hanging. Hostgator sent me this. Any Ideas

Hello,

I checked our kill logs and I'm seeing the follow SQL query being killed:

[ 1443553742 ] [ qsi1_opencart ] [ 263475059 ] [ 195 ] [ qsi1_opencart ] [ Query ] [ KILLED ] [ 'gator4003.hostgator.com' ] [ SELECT c.customer_id, CONCAT(c.firstname, ' ', c.lastname) AS customer, c.email, cgd.name AS customer_group, c.status, COUNT(o.order_id) AS orders, SUM(op.quantity) AS products, SUM(o.total) AS `total` FROM `oc_order` o LEFT JOIN `oc_order_product` op ON (o.order_id = op.order_id)LEFT JOIN `oc_customer` c ON (o.customer_id = c.customer_id) LEFT JOIN `oc_customer_group_description` cgd ON (c.customer_group_id = cgd.customer_group_id) WHERE o.customer_id > 0 AND cgd.language_id = '1' AND o.order_status_id > '0' GROUP BY o.customer_id ORDER BY total DESC LIMIT 0,20 ]

When trying to run this query, I couldn't even get it to complete. I tried creating an Index but it's still presenting the same issues. I also tried degfragmenting some of your tables without any luck.

You might want to pass this on to your developer to see if there's any way to avoid this query or change it.

Regards,
------------
Paul B.
Linux Systems Administrator
Austin, TX US

Hi, how large is your database that this query uses? Usually it's the database size that's causing it. Or it could be the query itself that's making it slow since it has a lot of variables. You might want to search about optimizing MYSQP queries.

Thanks for the reply. Not that big really. I think this is a hostgator issue on that server but they said they optimized MySQL for me.