Cleaning Up Magento’s Database Logs for Faster Performance

There are lots of ways to speed up your Magento’s database but the 1st way is always to clean it Logs.

Magento is very good, robust and highly stable eCommerce system but it’s database is not as efficient as it should be. Almost every operation with database for every user is written down into logs tables and sometimes these log tables have millions of records and take hundreds megabytes of disc space. Cleaning logs regularly helps in reducing latency of query execution and hence improves performance. Regular database cleaning can tidy up Magento database and all unused, cluttered or over encumbered records are sorted out.

Here are the main log-tables in Magento’s database that need to be cleaned:

aw_core_logger,
catalog_compare_item,
dataflow_batch_export,
dataflow_batch_import,
index_event,
log_customer,
log_quote,
log_summary,
log_summary_type,
log_url,
log_url_info,
log_visitor,
log_visitor_info,
log_visitor_online,
report_event,
report_viewed_product_index,
report_compared_product_index

So we can just go to the site’s cPanel and then to the phpMyAdmin, select out database, go to the “SQL” section and execute next code in it (just change “dbprefix_” on yours):

TRUNCATE `dbprefix_aw_core_logger`;
TRUNCATE `dbprefix_catalog_compare_item`;
TRUNCATE `dbprefix_dataflow_batch_export`;
TRUNCATE `dbprefix_dataflow_batch_import`;
TRUNCATE `dbprefix_index_event`;
TRUNCATE `dbprefix_log_customer`;
TRUNCATE `dbprefix_log_quote`;
TRUNCATE `dbprefix_log_summary`;
TRUNCATE `dbprefix_log_summary_type`;
TRUNCATE `dbprefix_log_url`;
TRUNCATE `dbprefix_log_url_info`;
TRUNCATE `dbprefix_log_visitor`;
TRUNCATE `dbprefix_log_visitor_info`;
TRUNCATE `dbprefix_log_visitor_online`;
TRUNCATE `dbprefix_report_event`;
TRUNCATE `dbprefix_report_viewed_product_index`;
TRUNCATE `dbprefix_report_compared_product_index`;

Performing this regularly will definitely improve your Magento store’s performance and efficiency. You can setup up scripts to do this automatically using cron.

Bookmark the permalink.

One Comment

  1. There are 3 ways for cleaning Database

    By Admin
    By Database
    By Extensions

    Most of the Expert are not prefer 3rd party extension. I used to cleaning log via admin,

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

    Please let me know is there anything is remaining
    Resource: http://www.cloudways.com/blog/cleanup-magento-database-log/

Leave a Reply to Fahad Rafiq Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>