category page slow down shop

[viktor123]

I received msg from my hosting provider that this query should be optimized, because it is eating up my server process time. I ordered more memory for my VPS, but that's just a quick solution. This query comes from function getProducts(.....) from Category.php This usually appears when I call a category page, and especially a category with products > 300 and with 5 subcategories. On my other categories, this slowing down doesn't appear, only on the one with the more products and subcategories. 

My query that takes 14 seconds
 

# Time: 130903 15:12:10

# User@Host: --------------[---------] @ localhost []

# Query_time: 13.857460  Lock_time: 0.000255 Rows_sent: 390  Rows_examined: 6379516

SET timestamp=1378210330;

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`,

pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,

il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,

DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),

INTERVAL 30

DAY)) > 0 AS new, product_shop.price AS orderprice

FROM `ps_category_product` cp

LEFT JOIN `ps_product` p

ON p.`id_product` = cp.`id_product`

INNER JOIN ps_product_shop product_shop

ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)

LEFT JOIN `ps_product_attribute` pa

ON (p.`id_product` = pa.`id_product`)

LEFT JOIN ps_product_attribute_shop product_attribute_shop

ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1)

LEFT 

JOIN ps_stock_available stock

ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1  )

LEFT JOIN `ps_category_lang` cl

ON (product_shop.`id_category_default` = cl.`id_category`

AND cl.`id_lang` = 4 AND cl.id_shop = 1 )

LEFT JOIN `ps_product_lang` pl

ON (p.`id_product` = pl.`id_product`

AND pl.`id_lang` = 4 AND pl.id_shop = 1 )

LEFT JOIN `ps_image` i

ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop

ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1)

LEFT JOIN `ps_image_lang` il

ON (image_shop.`id_image` = il.`id_image`

AND il.`id_lang` = 4)

LEFT JOIN `ps_manufacturer` m

ON m.`id_manufacturer` = p.`id_manufacturer`

WHERE product_shop.`id_shop` = 1

AND cp.`id_category` = 2 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") GROUP BY product_shop.id_product ORDER BY cp.`position` ASC

LIMIT 0,1000000;

 

Here you can see the load speed

The site was recently upgraded from 1.4.10 and so far almost everything is fixed, except for the load time of some pages.

 

The site is hosted on VPS with enough memory.

Configurations are as follows:

Recompile templates if the files have been updated 

Cache - Yes

Use CCC for all

Disabled Caching system

D

Edited September 4, 2013 by viktor123 (see edit history)

[vekia]

youve got some overrides of Category class?

[viktor123]

Hi Vekia, no, I don't have any overrides. I had followed this upgrade . Do you want to have a closer look at the account? Would appreciate solving the problem, because my customers complain for having to wait too long on page upload. 

 

Btw, I had executed this query directly on phpmyadmin and it took the same amount of time to get the results. 

When I call category page, it should be cached and the next time I recall it, the app must read the cached copy, not to query the DB again.

Edited September 4, 2013 by viktor123 (see edit history)

[logz05]

Hi Viktor

 

I have pretty much the same problem - I too have narrowed it down to the same query.  Did you solve it?