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.
