Post by bkpie » Wed Sep 30, 2015 8:35 pm

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

Newbie

Posts

Joined
Wed Apr 29, 2015 11:06 pm

Post by markjacks » Wed Sep 30, 2015 10:47 pm

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.

User avatar
New member

Posts

Joined
Sun Nov 30, 2014 1:48 pm

Post by bkpie » Tue Oct 06, 2015 1:31 am

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.

Newbie

Posts

Joined
Wed Apr 29, 2015 11:06 pm
Who is online

Users browsing this forum: No registered users and 12 guests