Magento stores old sales quotes dating back to the first day you launched your site. Sooner than you know it, you can be up to a couple million quotes that have never been processed but which can take up significant space, and slow your database.
This SQL query will clean out quotes (and their related content in other tables) that are older than 60 days.
DELETE FROM quote WHERE updated_at < DATE_SUB(Now(),INTERVAL 60 DAY)
Make sure to get this task setup on a cron job so that you don’t have to think about it again and your Magento store can keep running fast!
Frustrated by the ridiculous database log management (or the lack thereof) in Magento 2? Us too. We keep hoping that with each iteration, 2.1, 2.2, 2.3 they would finally consider that actions that took place four years ago don’t need to be cluttering our databases. I digress. If you’re looking for a quick sql solution to clean up millions of database rows in your Magento installation, look no further:
SET foreign_key_checks = 0; TRUNCATE customer_log; TRUNCATE customer_visitor; TRUNCATE report_compared_product_index; TRUNCATE report_event; TRUNCATE report_viewed_product_aggregated_daily; TRUNCATE report_viewed_product_aggregated_monthly; TRUNCATE report_viewed_product_aggregated_yearly; TRUNCATE report_viewed_product_index; TRUNCATE product_alert_stock; TRUNCATE search_query; TRUNCATE catalogsearch_fulltext_scope1; TRUNCATE sales_bestsellers_aggregated_yearly; TRUNCATE sales_bestsellers_aggregated_monthly; TRUNCATE sales_bestsellers_aggregated_daily; SET foreign_key_checks = 1;