Magento MySQL Database Optimization with Log Cleaning

Magento, as is well known, places a high demand on web servers due to its extensive and secure modular structure. An eCommerce system must have a comprehensive tracking system that can track client activity, orders, and other pertinent data, and Magento has all of this functionality as well as storing all information in MySQL separate database tables in the form of logs. Magento features around ten log tables that must be optimized on a regular basis.
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.

I’ve covered everything relevant to Magento database log cleaning and optimisation in this post below, step by step: It is generally a good idea to backup your database before beginning the database optimisation procedure.

The following are the log tables controlled by the Magento system. It can keep track of customer activities, login information, customer product comparisons, visitor information, log URL, frequently seen items, and so on. There are three methods for optimizing and cleaning logs:

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.

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 choose this route, make sure you understand database and phpMyAdmin structure since you will be doing operations with the database, which is the heart and soul of any programme. Before performing any cleanup, always backup the database. You may access the database using SSH command line or phpMyAdmin via your web hosting cPanel.

You must empty the tables mentioned below using the phpMyAdmin interface. click a table and then click “Empty” from the action menu at the bottom of the page. You will then see a confirmation screen; simply hit “yes” choices, and you are done.

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