How to clean Magento Database Logs to improve performance?

Magento is a pretty stable eCommerce system but the database is not that efficient as it should be.  A major performance boost can be achieved by cleaning the logs regularly. The outcome is that it helps in reducing latency of query execution and thus improves performance. Regular database cleansing can tidy up Magento database and all unused, cluttered or over encumbered records are sorted out.

If you are unsure on what you are doing, its better you backup magento db before proceeding with the below steps. You can use mysqldump command or export option in phpMyAdmin to backup the database.

There are two different options to clean up your logs:

1.Clean your logs via Magento Admin Panel “Log Cleaning”
2.Clean up your database tables directly via phpmyadmin or SSH MySQL.

The following tables are used & managed by Magento’s log cleaning functions:

• 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

Cleaning Logs via Magento Admin Panel

To activate log cleaning option in Magento just do the following:

1.     Log on to your Magento Admin Panel.
2.     Go to System => Configuration
3.     On the left under Advanced click on System (Advanced = > System)
4.     Under system you will see “Log Cleaning” option
5.     Fill the desired “Log Cleaning” option values and click Save.

Cleaning Logs via phpMyAdmin

This method is only recommended for those who are comfortable with mysql and queries.

1.     Open the database in phpMyAdmin
2.     In the right frame, click on the boxes 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

3.  Look to the bottom of the page, then click the drop down box that says “with selected”

and  click empty.
4.  Click Yes on confirmation screen, and this will truncate all the selected tables.

Please note we are only truncating (empty) the selected tables and not dropping them. You need to be careful while doing this. ( this is why i recommended cleaning via Magento admin area)

Performing this regularly will definitely improve your Magento store’s performance and efficiency.