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
- While in the Magento Admin click on System, then click configuration.
- In the menu on the left under Advanced click System.
- 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.