Jump to content

Query da 55 milioni di record


Recommended Posts

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
# [email protected]: fertecni_root[fertecni_root] @ srv-hp4.netsons.net []
# 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`
SELECT pr.`id_product`, t.`totalQuantitySold`, t.`totalPriceSold`
FROM `ps_product` pr
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?

Share this post

Link to post
Share on other sites

  • 2 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More