We migrated from Magento 1.9 to Magento 2x earlier in the year.
I've noticed the tables sales_bestsellers_aggregated_monthly and sales_bestsellers_aggregated_yearly tables keep getting larger. They're responsible for over half of the database size.
sales_bestsellers_aggregated_monthly is 3.6gb
sales_bestsellers_aggregated_yearly is 1.3gb
In Magento 1 they are only 11mb and and 44mb respectively, why are they so much larger in Magento 2?
Is all this data needed? What is the purpose of these tables and why should they be so large? Is it just for report data?
Greetings of the day!
This facility is provided by default Magento. It is possible due to cron is running. It might be possible that the cron is running several times and the same data is loading multiple times. This can be one of the reasons for your table's big size.
Why this table? then here is the and that we no longer need to determine the total of all the orders to see which products are sold the most. By the variety of aggregation data, we’re able to get the most popular products not only from the beginning but for each specific day, month, or year.
This is a default Magento 2 table so you cannot remove it. But you can delete all the data from the table through the following command.
TRUNCATE TABLE sales_bestsellers_aggregated_daily; TRUNCATE TABLE sales_bestsellers_aggregated_monthly; TRUNCATE TABLE sales_bestsellers_aggregated_yearly; ALTER TABLE sales_bestsellers_aggregated_daily AUTO_INCREMENT=1; ALTER TABLE sales_bestsellers_aggregated_monthly AUTO_INCREMENT=1; ALTER TABLE sales_bestsellers_aggregated_yearly AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS=1;
Hope this will help you to solve your problem.
If not, feel free to contact us.
Click KUDOS and accept as a solution.
Yes, I had to deal with the similar table problem. Because the default Magento 2 table cannot be removed directly, I followed the contributor's commend steps advice and deleted all of the data from my ac market table. I still require certain information, but this is the only solution I found.
Since posting this my tables have grown considerably again.
sales_bestsellers_aggregated_monthly is 4.9gb
sales_bestsellers_aggregated_yearly is 1.6gb
Why are these tables growing so quickly? We don't have a huge amount of orders, how can they be this big?