Query da 55 milioni di record

[crazy83]

Ho un sito di ecommerce con prestashop su netsons. Sono mesi che funziona sempre tutto senza grossi problemi, ma l'altro ieri me l'hanno bloccato perchè c'è stata una query che ha richiesto risorse oltre il limite imposto. Vi faccio vedere la query incriminata:

# Time: 101207 8:35:54
# User@Host: fertecni_root[fertecni_root] @ srv-hp4.netsons.net [10.0.4.3]
# Query_time: 27.479576 Lock_time: 0.047709 Rows_sent: 40 Rows_examined: 55600465
use fertecni_shop;
SET timestamp=1291707354;
SELECT ca.`id_category`, CONCAT(parent.name, ' > ', calang.`name`) as name,
IFNULL(SUM(t.`totalQuantitySold`), 0) AS totalQuantitySold,
ROUND(IFNULL(SUM(t.`totalPriceSold`), 0), 2) AS totalPriceSold,
(
SELECT IFNULL(SUM(pv.`counter`), 0)
FROM `ps_page` p
LEFT JOIN `ps_page_viewed` pv ON p.`id_page` = pv.`id_page`
LEFT JOIN `ps_date_range` dr ON pv.`id_date_range` = dr.`id_date_range`
LEFT JOIN `ps_product` pr ON CAST(p.`id_object` AS UNSIGNED INTEGER) = pr.`id_product`
LEFT JOIN `ps_category_product` capr2 ON capr2.`id_product` = pr.`id_product`
WHERE capr.`id_category` = capr2.`id_category`
AND p.`id_page_type` = 1
AND dr.`time_start` BETWEEN '2010-10-01 00:00:00' AND '2010-12-31 23:59:59'
AND dr.`time_end` BETWEEN '2010-10-01 00:00:00' AND '2010-12-31 23:59:59'
) AS totalPageViewed
FROM `ps_category` ca
LEFT JOIN `ps_category_lang` calang ON (ca.`id_category` = calang.`id_category` AND calang.`id_lang` = 3)
LEFT JOIN `ps_category_lang` parent ON (ca.`id_parent` = parent.`id_category` AND parent.`id_lang` = 3)
LEFT JOIN `ps_category_product` capr ON ca.`id_category` = capr.`id_category`
LEFT JOIN (
SELECT pr.`id_product`, t.`totalQuantitySold`, t.`totalPriceSold`
FROM `ps_product` pr
LEFT JOIN (
SELECT pr.`id_product`,
IFNULL(SUM(cp.`product_quantity`), 0) AS totalQuantitySold,
IFNULL(SUM(pr.`price` * cp.`product_quantity`), 0) / c.conversion_rate AS totalPriceSold
FROM `ps_product` pr
LEFT OUTER JOIN `ps_order_detail` cp ON pr.`id_product` = cp.`product_id`
LEFT JOIN `ps_orders` o ON o.`id_order` = cp.`id_order` LEFT JOIN `ps_currency` c ON o.id_currency = c.id_currency
WHERE o.valid = 1
AND o.invoice_date BETWEEN '2010-10-01 00:00:00' AND '2010-12-31 23:59:59'
GROUP BY pr.`id_product`
) t ON t.`id_product` = pr.`id_product`
) t ON t.`id_product` = capr.`id_product`
GROUP BY ca.`id_category`
HAVING ca.`id_category` != 1 ORDER BY `totalPriceSold` DESC LIMIT 0, 40;

Qualcuno sa dirmi cosa può aver causato il problema?
Grazie

[jacky75]

A me sembra la query della funzione "getData()" di statsbestcategories.php. Hai queste statistiche attive?

[disarci]

La tabella delle statistiche....va vuotata almeno una volta al mese (o trasferita) nei siti ben visitati!