cancel
Showing results for 
Search instead for 
Did you mean: 

Slow querie for indexing

SOLVED

Slow querie for indexing

Hello,

 

I have question on how to speed up this querie:

 

SELECT `rt`.`avg_percent` AS `rating` FROM `catalog_product_entity` AS `e`
INNER JOIN (SELECT `product`.`entity_id` AS `entity_pk_value`, `rt`.`avg_percent` FROM `catalog_product_entity` AS `product`
LEFT JOIN (SELECT `rova`.`entity_pk_value`, avg(percent_approved) AS `avg_percent` FROM `rating_option_vote_aggregated` AS `rova` WHERE (rova.store_id = 3) GROUP BY `rova`.`entity_pk_value`) AS `rt` ON product.entity_id = rt.entity_pk_value GROUP BY `product`.`entity_id`) AS `rt` ON e.entity_id = rt.entity_pk_value WHERE (`e`.`entity_id` = '488060')

Right now my server does about 160 queries in a minute, but since I have almost 500 000 products it will still take about 52 hours. 

 

While that queries runs MySQL usage is 1cpu core 100% and memory usage 19GB or approximately 15%. 

 

I use akeneo to update products in my Magento 2 website, even if it updates only 5000-20000 products it will run that queries on all of the products. 

 

Magento version 2.4.3 and all the programs are as shown here

https://devdocs.magento.com/guides/v2.4/install-gde/system-requirements.html

 

The only difference is Elasticsearch that is 7.9.3, not 7.9.0.

 

Server is AMD Ryzen 5950X, 128gb EEC, 2x 2tb nvme ssd hosted at hetzner. 

Ubuntu 20.04, Apache2, MySQL 8

 

I have mysqltuner installed and have been changing settings according to that. I had at first 130 queries in a minute now is 160, but can't get any higher. 

 

Maybe you have some recommendations? Maybe there are some Magento settings I can change. I come from Prestashop and it is my first Magento store. 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Slow querie for indexing

Found solution. It was custom magento ajax layred navigation module that came with theme. 

It is Codazone theme Infinity (top 5 modules on themeforest for magento). When I disabled that module all products where indexed in 5 minutes. 

 

 

View solution in original post

1 REPLY 1

Re: Slow querie for indexing

Found solution. It was custom magento ajax layred navigation module that came with theme. 

It is Codazone theme Infinity (top 5 modules on themeforest for magento). When I disabled that module all products where indexed in 5 minutes.