Optimize magento database Log Cleaning
Magento maintains several tables for logging. These tables log things such as customer accesses and which products have been compared. Magento has a mechanism for cleaning these logs regularly, but unfortunately this feature is disabled by default and most customers do not turn it on. There are three ways to clean out these tables: via Log Cleaning in the Magento Admin, via log.php in the ../shell directory, and manually via phpMyAdmin or mysql client.
The following tables are managed by Magento’s Log Cleaning function:
log_customer log_visitor log_visitor_info log_url log_url_info log_quote report_viewed_product_index report_compared_product_index report_event catalog_compare_item< !−−more−−>
Let ‘s execute this SQL to clear and optimize them
TRUNCATE dataflow_batch_export; TRUNCATE dataflow_batch_import; TRUNCATE log_customer; TRUNCATE log_quote; TRUNCATE log_summary; TRUNCATE log_summary_type; TRUNCATE log_url; TRUNCATE log_url_info; TRUNCATE log_visitor; TRUNCATE log_visitor_info; TRUNCATE log_visitor_online; TRUNCATE report_viewed_product_index; TRUNCATE report_compared_product_index; TRUNCATE report_event; TRUNCATE index_event; TRUNCATE catalog_compare_item;
Optimize magento database Log Cleaning via Admin
- In the Magento Admin, go to System > Configuration.
- In the left menu under Advanced click on System.
- Under “Log Cleaning”, change “Enable Log Cleaning” to YES and configure the Save Log for 15 days:
- Click “Save Config”.
Optimize magento database Log.php
The shell utility log.php can be configured as a cron job or run manually to clean on-the-fly.
From the Magento root directory, type:
php −f shell/log.php clean
The –days switch can be used to specify how many days back to save.
Manual Cleaning via PhpMyAdmin.
This is the most efficient way to clean the logs for those more comfortable working with databases. It is faster than the built-in Magento tools, and it allows you to clean a couple of other tables not included in those tools.
- Open the database in PhpMyAdmin via the Siteworx Control Panel.
- In the right (main) frame, select the check box for the following tables:
dataflow_batch_export dataflow_batch_import log_customer log_quote log_summary log_summary_type log_url log_url_info log_visitor log_visitor_info log_visitor_online report_viewed_product_index report_compared_product_index report_event
- At the bottom of the page, click the drop-down box that says “With Selected:” and select “Empty”.
- A confirmation screen will appear. Click “Yes”. This will truncate all of the selected tables.