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
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.
Solved! Go to Solution.
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.