Magento MySQL Database Optimization with Log Cleaning

Talk to Experts Need expert help? Don’t hesitate to talk.

You can do direct email atinfo@mconnectmedia.com

WE'RE HERE FOR YOU

We would love to hear about your Magento project, challenge, or opportunity. We'll respond within 24 hours!

As its known thing that Magento is heavy on web servers because of its vast and secured modular structure. An eCommerce system must have a robust tracking system which can track customer activities, and orders and other relevant data tracking and Magento have all this functional and storing every information in MySQL different database tables in the form of logs. Magento has around 10 logs tables which need to be optimized periodically.

MySQL database optimization is the best way to organize your website in a better idea which can give a better experience to end user who is visiting and buying from your professionally designed eCommerce website.

Magento Database Optimization

Here in this post below, I’ve listed everything related to Magento database log cleaning and optimizing way step by step: It’s always best practice to back up your database before doing database optimization process.

Here are logs tables managed by the Magento system. It can track customer activities, login information, customer products compare, visitor’s information, log URL, recently viewed, etc. There is three way to optimize and log clean:

  1. Magento Default Log Cleaning Functionality
  2. Create Log Cleaning PHP script with cron Schedule
  3. Manually clear tables using phpMyAdmin.

Here are the tables which required optimization in Magento:

  1. log_customer
  2. log_visitor
  3. log_visitor_info
  4. log_url
  5. log_url_info
  6. log_quote
  7. report_viewed_product_index
  8. report_compared_product_index
  9. report_event
  10. catalog_compare_item

Execute below MySQL queries for the log cleaning of Magento database:

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

Magento Database Optimization and log cleaning via Magento Admin Default Functionality

  1. Log in to Magento Admin Panel
  2. Go to System>>Configuration
  3. From left sidebar click system under the advanced tab
  4. After clicking on the system menu, you will see a block opened from there select log clearing tab and set “Enable Log clearing ” options to “Yes” and set up your desired log cleaning period automatically.
  5. After the following step for just click on save config, and you are all set.

Magento Admin

Optimize Magento Database using log_cleaning script

You can set manual cron script which can run periodically, mention shell command in it and put this script file in root folder if your Magento and set cron schedule on the web server to run this file for log cleaning.

You can specify your time on the cron so it will run as per your schedule rules:
Here is the command line – php -f shell/log.php clean

Manually clean your Magento Database Log tables for optimization using phpMyAdmin

If you are going in this way, then make sure that you are aware of database and phpMyAdmin structure as you are going to perform tasks with the database which is heart and soul for any application. Always backup database before doing any cleaning work. You can go to phpMyAdmin via your web hosting cPanel, or you can also access the database via SSH command line.

You need to empty below-listed tables using phpMyAdmin interface where you will have to select a table and from the action dropdown at the bottom of the page select “Empty” and then you will see a confirmation page just press “yes” options, and you are all set.

  • 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

To know more on optimization process just put your comment on this blog and I’ll provide you answers on it. Please refer this link to get platinum level Magento technical support services.

Feel free to contact us for any Magento Website Optimization services!

Need Magento expert help?

We provide result-driven solutions to expand the competency level and productivity.

Instant Help CenterAvailable!

Monday to FridayResponse promised within 24 hours!

Call Us

+1 319 804-8627

5 comments

  1. Great solution Jiten. Those tables are quite annoying and they actually take the site down if we don’t keep an eye on them. Cheers

  2. Nice Solution but i want to optimize MySQL query which is generate in hourly like slow.log in /var/log/mysql/slow.log
    Can we optimize the mysql query. Please provide me good suggestion.

Load Comments

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

  • Worried for deadlines? Our Magento Experts are effortlessly Working from Home.
  • Check out our Magento Developer Hiring Packages for Agency as well as individuals.
View Packages

Talk to Experts Need expert help? Don’t hesitate to talk.

You can do direct email atinfo@mconnectmedia.com

WE'RE HERE FOR YOU

We would love to hear about your Magento project, challenge, or opportunity. We'll respond within 24 hours!

Please fill this form, Mr.Yogesh will reply by email asap.


Please fill this form, Mr.Darshit will reply by email asap.


Please fill this form, Mr.Jayesh will reply by email asap.


Please fill this form, Mr.Jiten will reply by email asap.


Do you know Magento 1 support will end in June 2020?
Magento 1 to Magento 2 Migration Service Error
So, Don't take the risk

Make a move & Migrate to Magento 2

  • Magento 1 to Magento 2 Migration Service - Zero Downtime

    Zero Downtime

  • Magento 1 to Magento 2 Migration Service - Timely Delivery

    Timely Delivery

  • Magento 1 to Magento 2 Migration Service - Stores Upgraded

    36+ Stores Upgraded

  • Magento 1 to Magento 2 Migration Service - Zero Data Loss

    Zero Data Loss

  • Magento 1 to Magento 2 Migration Service - Magento Developers

    Certified Magento Developers

  • Magento 1 to Magento 2 Migration Service - After Support

    60 Days After Support

How much it Cost?