How to perform Magento Database Maintenance?

Magento does many things well, but maintaining an efficient database is not one of them.

So you should learn how to perform Magento Database Maintenance?

To perform Database Maintenance, you need to clean the log files. You can clean the log files with the admin panel, or by the cleaning function of the Magento. phpMyAdmin also offers a way to clean the log files that can help in maintaining the database of Magento.

Having many products is a good reason to have a large database. Sadly, this is not the only way your database can become large and sluggish. Maintaining the database through log cleaning can result in a dramatic improvement in site performance and latency. This guide explains how to optimize a large and potentially inefficient Magento database.

Attention: Always backup your data before performing any operations on the database.

Log cleaning

Magento maintains several tables for logging. These tables log things such as customer accesses and frequently-compared products.

Magento has a mechanism for cleaning these logs regularly, but unfortunately, this feature is disabled by default and most customers do not enable it.

There are three ways to clean out these tables to perform Magento Database Maintenance:

• Log cleaning in the Magento Admin
• Log.php in the ../shell directory
• Manually via phpMyAdmin or MySQL client

Magento’s 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

Magento Log cleaning via administrator Interface

1. From the Magento administrator interface, go to System > Configuration.
2. In the left menu under Advanced, click System.
3. Under Log Cleaning, change Enable Log Cleaning to Yes and configure the Save Log for 15 days:

Log.php

You can either configure the shell utility log.php  as a cron job or run manually to clean on-the-fly.

1. From the Magento root directory, type the command:

php -f shell/log.php clean

2.  Use the –days switch to specify how many days of history to save.

Manual cleaning via phpMyAdmin

This is the most efficient way to clean the logs for those more comfortable working with databases.

It is faster than the built-in Magento tools and it allows you to clean other tables not included in those tools.

This procedure will consolidate the data inside those tables, often decreasing database size by as much as 95% and greatly reducing query times.

1. Open the database in phpMyAdmin.
2. In the right frame, select the checkbox 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. At the bottom of the page, click the drop-down box With Selected and select Empty.
4. A confirmation screen will appear. Click Yes. This will truncate all of the selected tables.
5. Click the Structure tab at the top of the page.
6. Select the same tables as you did in step 2, then under the With the Selected drop-down menu, select Optimize.

Magento Database Maintenance

Hope now you know how to perform Magento Database Maintenance.

If still any problem, leave the problem in comments.