Magento Database Maintenance

Magento Database Maintenance

Magento is brilliant, but when it comes to database maintenance, it’s not the world’s most fantastic resource. If you have a large selection of products then it makes sense that your databases are large. However, these can be vastly increased with useless information causing them to become sluggish. Log clearing can help you keep your databases the size that they should be.

Thankfully, there’s plenty of ways you can do it yourself to keep your site running quickly. Remember to back up all of your data before trying these out.

Log Cleaning

Magento has a number of tables logging information such as customer accesses and frequently compared-products. While these tables have the ability within Magento be cleaned regularly, this is not set up as default (why??).

These tables are cleaned using the 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

There are three ways to clean these tables.

Log cleaning in the Magento Administrator Interface

  1. In the Magento administrator interface, go to System > Configuration.
  2. Click System in the left menu under Advanced
  3. Enable Log Cleaning by clicking Yes under Log Cleaning.
  4. Set it to clean logs every two weeks.
  5. Save Config.

Log.php

Either set up a cron job or you can perform this method manually if you need.

  1. In the Magento root directory, you need to type:

php -f shell/log.php clean

The -days switch lets you set the days you want to save.

Manual cleaning with phpMyAdmin

If you want to clean all the tables, then this is the best option.

  1. Open the database in phpMyAdmin
  2. Select 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

  1. Click Empty from the With Selected list at the bottom of the page.
  2. Confirm Yes.
  3. Click the Structure tab and select the same tables.
  4. Click Optimise from the With Selected list at the bottom of the page.

Author Owen Radford

More posts by Owen Radford