Database clean-up for Magento CE 1.X

If you have a Magento CE 1.X webshop then that means it might also be old by now. Does it runs and loads slower with each passing day?
This slow-down could be due to the size of your Magento database. The size of the database can depend on alot of factors, including number of products in your webshop. With time, Magento database also ends up collecting alot of junk logs and un-necessary data. The bloated database can eat-up alot of disk-space of your server and it can slow down your webshop's frontend and backend. A slow webshop is never good for sales and business. Therefore, it is highly recomened that you regularly clean-up your Magento database. There are many easy ways to do that.

We will explain a few of them in this post today. If you are a developer then you can use one of the first 2 methods. If you are a non-technical webshop administrator then go for the third option. Make sure that you take a back-up of your database before running any script of queries on it.

1. Via SSH
Login to the server where your Magento webshop is hosted and then navigate to your Magento root directory using 'cd' command. From Magento root directory, you can run following command:

php -f shell/log.php clean

This will run the standard log clea-up script in Magento. You can also setup a cron job to do this periodically and automatically.

2. Via PHPMyAdmin
If you have database access then open it in PHPMyAdmin and run following query:

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;

Once run successfully, it will display a success message like this:

You can also select following database tables manually and at the bottom you can select ‘Empty’ from the drop-down ‘With selected and click 'Yes'. This will clear all these 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. Automatic log cleaning
You can also setup automatic cleanup of logs from Magento backend.
Login to your Magento CE 1.X backend > go to System > Configuration > Advance > System > Log cleaning > Enable Log Cleaning = Yes > Setup time and frequency for this clean-up > Save config.

It can also send you error notification emails if there are any problems with the clean-up. We recommend you set it to clean the database atleast once every month.

Following tables are cleaned up by this automatic feature:

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

By doing this simple database clean-up, you can decrease the size of your database by upto 70%. Lighter database performs faster and faster webshops sell more.

Feel free to contact Hungersoft if your Magento webshop is still running slow. We are experts at Magento speed optimization and can speed-up any webshop.