MySQL process issue - running for hours!

[rootus]

Hi guys, I encounter the following issue on a customer's server:

# mysql -Bse "show full processlist \G" |head -n 30
*************************** 1. row ***************************
     Id: 1680
   User: someuser
   Host: localhost
     db: somedb
Command: Query
   Time: 3014
  State: Copying to tmp table
   Info: SELECT p.*, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`name`, pa.`id_product_attribute`,
                tax.`rate`, i.`id_image`, il.`legend`, m.`name` manufacturer_name
            FROM ps_product p
            INNER JOIN `ps_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 6)
            LEFT JOIN `ps_tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
                                                                   AND tr.`id_country` = 36
                                                                   AND tr.`id_state` = 0)
            LEFT JOIN `ps_tax` tax ON (tax.`id_tax` = tr.`id_tax`)
            LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
            LEFT JOIN `ps_manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
            LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
            LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 6)
            WHERE p.active = 1 AND pa.quantity > 0 AND p.id_product in (select id_product from ps_category_product where id_category = 23) AND p.id_product IN (SELECT DISTINCT pa.id_product FROM ps_product_attribute pa LEFT JOIN ps_product_attribute_combination pac USING ( id_product_attribute ) WHERE  id_attribute =20 OR  id_attribute =8 GROUP BY id_product_attribute HAVING count( id_product_attribute ) =2) ORDER BY  p.id_product DESC
*************************** 2. row ***************************

Often I see in the processlist entries like the one above (it's not the only one, just an example). Basically you see a process running for almost an hour and this is because recently the db server was restarted.

It seems to me that it takes too much time to complete, there are around 5000 products in the store and the DB is ~ 600MB without truncating connections tables.

Obviously this causes mysql server and site to be slow as hell.

 

Do you have any idea what is the cause of this? what thiggers this whole mess? - I'm just a humble sysadmin trying to sort this one out, the developers hired by the customer let's say that are maybe not the best you can find...

 

PS: This is version 1.4.7 of the shop, for some reason the previous developers decided to manually edit the shop and at least now upgrading is not possible without destroying site's structure/functions.

 

Any hint/idea will be really apreciated.