Magento maintains several log tables for tracking purpose like customer access, products viewed, products compared etc. These tables grow in size day by day so if you have a large numbers of visitors on your website the size of these log tables may become large enough within a week slowing down your database. So you should perform database log cleaning on a regular basis – daily/weekly/monthly depending upon your website traffic.

There are three ways to clean out these tables:

1. Log Cleaning in the Magento Admin
2. Shell Utility log.php in the ../shell directory, and
3. Manually via phpMyAdmin or mysql client

Magento built in log cleaning utilities manage and clean the following tables:

• `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`

1.  Log Cleaning in the Magento Admin

Magento has a built in utility in admin to schedule log cleaning. By default its disabled and most of us are unaware of it. To enable it follow the below steps:

1. In the Magento Admin, go to System > Configuration.
2. In the left menu under Advanced click on System.
3. Under “Log Cleaning”, change “Enable Log Cleaning” to YES and configure the Save Log for 10 days.
4. Click “Save Config”

2. Shell Utility log.php in the ../shell directory

The shell utility log.php can be run manually to clean on-the-fly.

Using the command line tool go to {{MAGENTO_ROOT}}/shell folder and run the following command

php -f log.php -- clean

By default this will clean the log tables saving log entries for the number of days specified in admin settings under “Log Cleaning”.

You can even specify the days for which you want to save log in the following way

php -f log.php -- clean -- days 10

If you are at the {{MAGENTO_ROOT}} you will be running

php -f shell/log.php -- clean -- days 10

To see all the command line options available with log.php run

php -f shell/log.php -- help

Log table status can be checked with the following command

php -f shell/log.php -- status

The shell utility log.php can even be configured as a cron job.

3. Manually via phpMyAdmin or mysql client

You can use this method if you are  comfortable working with databases. This method is faster than the built-in Magento tools, and it allows you to clean a couple of other tables not included in those tools.

Using the “phpMyAdmin” open the database and then select and empty the following tables.

• `log_customer`
• `log_visitor`
• `log_visitor_info`
• `log_visitor_online`
• `log_quote`
• `log_summary`
• `log_summary_type`
• `log_url`
• `log_url_info`
• `sendfriend_log`
• `report_event`
• `dataflow_batch_import`
• `dataflow_batch_export`
• `index_process_event`
• `index_event`
• `report_viewed_product_index`
• `report_compared_product_index`
• `catalog_compare_item`
• `enterprise_logging_event` [Magento Enterprise Edition]
• `enterprise_logging_event_changes` [Magento Enterprise Edition]

If you want to use the mysql client then you can run the following commands

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE `log_customer`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;
TRUNCATE `log_visitor_online`;
TRUNCATE `log_quote`;
TRUNCATE `log_summary`;
TRUNCATE `log_summary_type`;
TRUNCATE `log_url`;
TRUNCATE `log_url_info`;
TRUNCATE `sendfriend_log`;
TRUNCATE `report_event`;
TRUNCATE `dataflow_batch_import`;
TRUNCATE `dataflow_batch_export`;
TRUNCATE `index_process_event`;
TRUNCATE `index_event`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `report_compared_product_index`;
TRUNCATE `catalog_compare_item`;

ALTER TABLE `log_customer` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_online` AUTO_INCREMENT=1;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
ALTER TABLE `log_summary` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
ALTER TABLE `dataflow_batch_import` AUTO_INCREMENT=1;
ALTER TABLE `dataflow_batch_export` AUTO_INCREMENT=1;
ALTER TABLE `index_event` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;
ALTER TABLE `report_compared_product_index` AUTO_INCREMENT=1;
ALTER TABLE `catalog_compare_item` AUTO_INCREMENT=1;

--
-- Enterprise Edition Begins
--
TRUNCATE `enterprise_logging_event`;
TRUNCATE `enterprise_logging_event_changes`;
ALTER TABLE `enterprise_logging_event` AUTO_INCREMENT=1;
ALTER TABLE `enterprise_logging_event_changes` AUTO_INCREMENT=1;
--
-- Enterprise Edition Ends
--

SET FOREIGN_KEY_CHECKS=1;

NOTE: Please take a backup of database when performing any operations on it.

Log cleaning is a maintenance activity and it should be performed regularly.