Layered navigation - very slow category page loading

[Marta]

Hello everyone!

I've got a shop running on 1.6.0.9 Presta and I've finally decided to upgrade it a bit 😉

I've chosen the Thirty Bees instead of 1.7 Presta and I am having a lot of doubts at the moment.

When I turn on the layered navigation module I have to wait almost a minute until the category with the highest amount of products gets displayed. (I've got about 15000 products)

The old Prestashop is working all right.

I've set the cache according to the TB blog article.

Has anyone faced a problem like mine?

 

My temporary address (TB) lampadina..kei..pl

My current Prestashop  https://www.lampadina.com.pl/lampy

 

You may try this category:

lampadina..kei..pl/22-zyrandole

https://www.lampadina.com.pl/lampy/22-zyrandole

 

(sorry for the double dots 😉 )

Thanks

Edited September 27, 2022 by Marta

[haylau]

Both installations run about the same speed for me. May be that the cache just needs to catch up

[datakick]

You should enable profiling, that will tell you the bottlenecks.

[Marta]

 

 

Main page:

 

One of the category pages:

What should I do now?

Thanks

[datakick]

Most of the time is spent on database queries. Look for the long running db queries.

[Marta]

Could you have a look, please? Profiling is set to true at the moment.

lampadina..kei..pl/22-zyrandole

Tables stress

192 product_shop

130 product

79 product_attribute_shop

78 stock_available

76 smarty_lazy_cache

67 feature_lang

67 specific_price

66 feature_product

66 feature_value_lang

66 product_lang

65 tbhtmlblock_hook

65 tbhtmlblock

65 tbhtmlblock_lang

61 feature

61 product_group_reduction_cache

61 specific_price_priority

61 feature_shop

16 product_attribute

13 category

12 category_lang

7 image_type

6 category_shop

5 layered_indexable_feature_value_lang_value

5 layered_indexable_feature_lang_value

5 feature_value

5 cat_restriction

5 module

5 currency_shop

4 hook

4 module_shop

4 shop_url

4 shop

4 layered_friendly_url

4 lang_shop

4 category_product

4 currency

3 customer

3 customer_group

3 lang

3 theme

3 category_group

3 image_lang

3 image_shop

2 cat_filter_restriction

2 smarty_last_flush

2 tax_rule

2 tax_rules_group

2 configuration

2 layered_indexable_feature

2 group

2 shop_group

2 country

2 country_lang

2 country_shop

2 hook_module

2 hook_alias

2 layered_indexable_attribute_group

2 meta

2 group_shop

2 layered_category

2 address

1 attribute_group

1 attribute_lang

1 attribute

1 product_attribute_combination

1 attribute_group_lang

1 manufacturer

1 configuration_lang

1 hook_module_exceptions

1 module_currency

1 group_lang

1 tax_lang

1 tax

1 module_group

1 image

1 meta_lang

1 theme_meta

1 product_reductions

1 themeconfigurator

[e-com]

Too many products in home page modules. Home page is not used to display all products we have in store, just advertising a few or a dozen. These modules do not have pagination so loading thousands 🙂 products takes so long.
In addition, it is a good idea to set PHP 7.4 and memory limit at least 512MB.

[Marta]

Thx, I've changed PHP to 7.4 and set the cache for 10 minutes in newproducts and bestsellers modules.

The home page is working much better now.

 

The problem with the categories and blocklayered still not solved. 😞

 

  

 

 

 

[datakick]

This sql takes the most time:

SELECT
	SQL_NO_CACHE p.*,
	product_shop.*,
	product_shop.id_category_default,
	pl.*,
	image_shop. `id_image` id_image,
	il.legend,
	m.name manufacturer_name,
	product_attribute_shop.id_product_attribute id_product_attribute,
	DATEDIFF(product_shop. `date_add`, DATE_SUB("2022-09-28 00:00:00", INTERVAL 210 DAY)) > 0 AS new,
	stock.out_of_stock,
	IFNULL(stock.quantity, 0) AS quantity,
	product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity
FROM
	tb_cat_filter_restriction cp
	LEFT JOIN `tb_product` p ON p. `id_product` = cp. `id_product`
	INNER JOIN tb_product_shop product_shop ON (product_shop.id_product = p.id_product
			AND product_shop.id_shop = 1)
	LEFT JOIN `tb_product_attribute_shop` product_attribute_shop ON (p. `id_product` = product_attribute_shop. `id_product`
			AND product_attribute_shop. `default_on` = 1
			AND product_attribute_shop.id_shop = 1)
	LEFT JOIN tb_product_lang pl ON (pl.id_product = p.id_product
			AND pl.id_shop = 1
			AND pl.id_lang = 1)
	LEFT JOIN `tb_image_shop` image_shop ON (image_shop. `id_product` = p. `id_product`
			AND image_shop.cover = 1
			AND image_shop.id_shop = 1)
	LEFT JOIN `tb_image_lang` il ON (image_shop. `id_image` = il. `id_image`
			AND il. `id_lang` = 1)
	LEFT JOIN tb_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
	LEFT JOIN tb_stock_available stock ON (stock.id_product = p.id_product
			AND stock.id_product_attribute = 0
			AND stock.id_shop = 1
			AND stock.id_shop_group = 0)
WHERE
	product_shop. `active` = 1
	AND product_shop. `visibility` IN("both", "catalog")
ORDER BY
	pl.name ASC,
	cp.id_product
LIMIT 0, 60

You need to investigate why. You should connect to your database and execute the query manually. Look how many rows are in individual tables. use EXPLAIN sql command to figure out why the hell it takes so much time. 

There will be either

• data issue
  • How many rows are in tb_cat_filter_restriction table?
  • How many products do you have?
  • Does it correlate with product_shop / product_lang table?
• or schema issue
  • missing indexes, maybe

Nobody without access to your db can help you more now. 

 

[Marta]

#1146 - Table 'baza_thirtybees.tb_cat_filter_restriction' doesn't exist

 

[datakick]

ouch, it's a temp table, created specially for every request. That's gonna be hard to investigate. You will need to simulate the process of BlockLayered::getProductByFilters method. That's probably too advanced, though.

[Marta]

Definitely too advanced but perhaps I could try if somebody told me how. 😉

I don't know what I have done wrong so installing from scratch and importing the data form my Presta table by table may not help. What would you say to that?

[Marta]

This query takes 77 seconds when I test it in phpmyadmin. However, the result is correct. (blocklayered off)

The tb_stock table is empty and so it is in my old Presta.

SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, IFNULL(product_attribute_shop.id_product_attribute, 0) AS 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`, image_shop.`id_image` id_image,
il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
DATEDIFF(product_shop.`date_add`, DATE_SUB("2022-09-28 00:00:00",
INTERVAL 210 DAY)) > 0 AS new, product_shop.price AS orderprice
FROM `tb_category_product` cp
LEFT JOIN `tb_product` p
ON p.`id_product` = cp.`id_product`
INNER JOIN tb_product_shop product_shop
ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `tb_product_attribute_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1)
LEFT JOIN tb_stock_available stock
ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1  AND stock.id_shop_group = 0  )
LEFT JOIN `tb_category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = 1 AND cl.id_shop = 1 )
LEFT JOIN `tb_product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = 1 AND pl.id_shop = 1 )
LEFT JOIN `tb_image_shop` image_shop
ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1)
LEFT JOIN `tb_image_lang` il
ON (image_shop.`id_image` = il.`id_image`
AND il.`id_lang` = 1)
LEFT JOIN `tb_manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = 1
AND cp.`id_category` IN (22) AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") GROUP BY cp.id_product ORDER BY pl.`name` ASC
LIMIT 0,60

 

[zen]

I couldn't find your TB instance

Quote

My temporary address (TB) lampadina..kei..pl

What cache did you turn on on back office ? only Smarty cache need to be ON.. all the ones in the bottom can be desactivated or it will make your site slower.

For the layered navigation I recommand you to NOT use the default module, specially on such a huge catalog, instead try this one that can be also triggered in search result pages and others pages, it's very efficient and easy to config instead of others I tried.

https://www.presteamshop.com/en/prestashop-modules/filter-products-pro.html

 

[Marta]

Just change double dots into single dots in the address. (I don't need any robots crawling in there)

I've just disabled the cache besides the smarty one. I've also set the default sorting by position in the category. Seems to work a bit faster.

The filter module is looking good. Thx

[zen]

3 minutes ago, Marta said:

Just change double dots into single dots in the address. (I don't need any robots crawling in there)

I did.. but seems like not in a right way. Now I can see your dev website online 🙂

be careful that it will not be indexed, use a robots.txt file that exclude crawling.

Also, on the footer you can put something else in the last column, there is way too much categories showing up there.. maybe just select few ones in non dynamic link... or something else, but that query is useless and repeated on each pages for nothing.

Edited September 29, 2022 by zen

[Marta]

I know. Changed.

I think the list of categories at the bottom is useful for mobile users.

[Marta]

Thank you @datakickand @zen for your help. The TB store has been published. 🙂

The shop started to work faster after I had disabled and then enabled the newproducts, featured and bestsellers blocks.

The filtering module by Presteamshop is great, perhaps not perfect but almost there 😉 https://www.lampadina.com.pl