Magento Database Cleaning

Magento is an excellent platform, but it unfortunately falls short when it comes to having an efficient database. When selling a myriad of products, it’s a smart idea to have a sizable database. Managing the database by performing regular log cleanings can drastically improve the performance of your website along with its latency. The following is a walk through of how to tidy up a Magento database that has become over encumbered.

For starters, it is imperative that you make a back up of all your files prior to doing anything in case something goes wrong. You can either perform an export of your database using phpMyAdmin, or you can submit a technical support request so that we can do it for you.

Magento manages several logging tables. These tables make logs of things like which products have been compared, customer accesses and other ancillary logs. Magento has a self cleaning program to clean this out regularly, but this feature is inactive by default, and most consumers wont turn it on. Here are three ways to clean out these tables. You can either access the “Log Cleaning” feature in the Magento Admin, or you could just manually do so via phpMyAdmin.

The tables below are managed by the Magento 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

Log Cleaning through the Admin

1. While in the Magento Admin click on System, then click configuration.
2. In the menu on the left under Advanced click System.
3. Where it says “Log Cleaning”, select yes to enable log cleaning, and configure the save your logs for 15 days. Then click save.

Manual Cleaning through phpMyAdmin

This is by far the most effective way to clean the logs for individuals that are more comfortable working with databases. It’s definitely quicker than the Magento tool, and it allows one to clean some of the tables that aren’t included in the Magento tool.

-Open the database in PhpMyAdmin in the cPanel Control Panel

-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

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

A confirmation screen will appear, click yes, and this will truncate all the tables you just selected

Note: It can be very easy to mistakenly delete a database instead of emptying it, you should be very careful and make sure that you are selecting the correct option.

Make sure you perform this regularly. We’ve seen a 3+ gigabyte database drop to less than 100 megabytes after proper log cleaning has been performed. This kind of maintenance is very important for the performance and health of your website and needs to be done periodically.

For an automated method to clean this up regularly, you can setup a cron job by following our instructions on our post about speeding up Magento commerce.