This website uses cookies

Our website, platform and/or any sub domains use cookies to understand how you use our services, and to improve both your experience and our marketing relevance.

📣 Try the fastest hosting platform with pay-as-you-go pricing & 24/7 expert support! MIGRATE NOW →

How to Clean Up and Optimize Your Magento Database Log For Faster Loading

Updated on December 23, 2021

6 Min Read

The success of your ecommerce store depends in part on the sizable databases that help you sell products and services online. The ecommerce website you run on Magento will also use these database logs. Over time, as your Magento store piles up a few GB’s worth of logs, it’s performance will start to slow down, and the deployed resources will see an increase in usages.

So it is essential you clean up these logs on a regular basis and optimize the store for faster loading. Not only does this significantly boost Magento store performance, but it also helps reduce the latency of query execution.

In this article, I’ll show you an easy way to clean your Magento database.

Magento Optimize Database Log

Magento maintains tables that create logs, such as logs for what products have been compared. While Magento does have a mechanism for cleaning these logs regularly, this feature is disabled by default and most store owners don’t turn it on. 

There are three ways to complete a Magento database cleanup:

  • Log Cleaning in the Magento Admin
  • Through log.php in the ../shell directory
  • Manually via phpMyAdmin or MySQL Manager

Magento’s log cleaning functions use and manage the following tables :

  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

Install Magento 2 in a Single Click

Simplified Magento hosting that takes away your hosting woes

Cleaning Magento Database Logs via Admin Panel

This straightforward method is useful for non-technical store owners who do not want to directly work around with the Magento stores database. You can follow the following steps to perform a complete Magento database cleanup of logs:

First log in to your Magento Admin Panel.

  • Go to System > Configuration.

    Magento Store Configuration
  • On the left-hand side, under Advanced, click on System (Advanced > System).

    Magento MySQL Cleanup
  • Under System, you see MySQL Message Queue Cleanup.

Magento MySQL Message Queue Cleanup

  • Set the values for all of these attributes: Successful Messages Lifetime, Retry Messages In Progress After, Failed Messages Lifetime, New Messages Lifetime. 

Note: In the screenshot above, I’ve left the values as default. You can change this as per your automatic clearance requirements.

  • Click “Save Config”.

Optimize Magento Speed Like a Pro

Subscribe now and get a free ebook to your inbox.

Thank You

Your Ebook is on it’s Way to Your Inbox.

Switch to Flat Catalog to Reduce Database Queries

Enabling a flat catalog helps reduce the database load time, and allows the system to call frequently used DB queries more quickly.

Large-scale Magento stores find this technique useful. The bigger the size of the Magento database, the bigger the impact. 

It’s quite simple to enable the Flat & Product Catalog option. Log in to your Magento 2 Admin Panel, go to Stores > Configuration > Catalog and make sure to switch to YES on both to  Use Flat Catalog Category and Use Flat Catalog Product. Then hit save.

Magento Flat & Product Catalog

Setup & Use the Latest Elasticsearch

On the Magento 2 Admin Panel, navigate to Store > Configuration, select the Catalog section, and expand the Catalog Search option

Magento search engine

Cloudways also offers integration with Elasticsearch for Magento store performance

Cloudways Elasticsearch

Scalable, Super Fast, & Secure Magento Hosting

Our clients love us because we never compromise on security, performance and support.

Update the Database Version

Upgrade your database to the latest version to improve performance, security and bug fixes. I would highly recommend that developers keep updating the databases (MySQL, MariaDB, or Percona) for read and write accessibility, improved speed, enhanced algorithms, and much more.

Magento Cleaning Logs via Cloudways Database Manager

If you are familiar with MySQL and queries, this method is quicker and more useful than the default Magento log cleaning. It lets you clean whatever log you like; you can even clean tables that are not included in default Magento log cleaning. I will be using the Cloudways Platform to perform this particular activity.

  • In the Cloudways platform, go to Applications. Under ACCESS DETAILS, tap the LAUNCH DATABASE MANAGER button.

Cloudways Server Access Details

On the right side of your MySQL manager, click on the below-mentioned boxes for the following tables:

  1. dataflow_batch_export
  2. dataflow_batch_import
  3. log_customer
  4. log_quote
  5. log_summary
  6. log_summary_type
  7. log_url
  8. log_url_info
  9. Log_visitor
  10. Log_visitor_info
  11. log_visitor_online
  12. report_viewed_product_index
  13. report_compared_product_index
  14. Report_event
  • Select all of the listed tables above in MySQL manager and click the Truncate button, as shown in the image below, to perform Magento truncate log tables:Database Truncate

Keep in mind that you need to truncate selected tables without dropping them. Be very careful when doing this.

Please note: Some statistical data will be lost if you clean these log tables, like guest information, or visit URL data. If this kind of information is useless to you, go ahead and clear the logs. If you are not sure what to erase, save a backup in case things do not go as planned. 

And that’s it. You have successfully optimized your Magento database.

Magento Database Optimization FAQs

How to Check Magento Database Status?

First you need to log in to the Magento server as, or switch to a user with permissions to write to the Magento file system.

If you use the bash shell, you can use the following syntax to switch to the file system owner:
su -s /bin/bash -c

To check the status of the Magento database, enter
bin/magento setup:db:status

This command has no arguments or options. Sample output would be like this:
All modules are up to date.

How to Import Products in Magento 2 Database?

Following are the steps that need to be followed in order to add import products in Magento 2 Database:

Step 1: Prepare the Data
Step 2: Configure Import Behavior
Step 3: Specify the Import File
Step 4: Check the Data and Finish the Importing
Step 5: View the Import History

Which database is my Magento using?

Magento uses MySQL, as it provides the flexibility of a fully customizable database system without a hefty price tag.

What is Magento security scan tool?

Magento has rolled out a new security scan tool that enables Magento merchants to regularly monitor their sites and receive updates regarding known security risks, malware, and unauthorized access.

Where is the Magento Database config located?

To get Magento DataBase config file go to: //app/etc/local. xml; having accessed Magento database config file location, edit local.

How do I find my Magento database name?

Navigate to your store core folder and open the env. php file under the app/etc folder. Find the next code, where database_name is the actual database name which you use for your Magento 2 store.

Book a Personalized Magento Product Demo

In a Nutshell

Clean your Magento database logs regularly for enhanced speed and performance, but be careful. It is better to back up your Magento-based ecommerce store before making any changes. With Cloudways, you can create the backup with just a single click. 

Share your opinion in the comment section. COMMENT NOW

Share This Article

Abdur Rahman

Abdur Rahman is the Magento whizz at Cloudways. He is growth ambitious, and aims to learn & share information about Ecommerce & Magento Development through practice and experimentation. He loves to travel and explore new ideas whenever he finds time. Get in touch with him at [email protected]

×

Get Our Newsletter
Be the first to get the latest updates and tutorials.

Thankyou for Subscribing Us!

×

Webinar: How to Get 100% Scores on Core Web Vitals

Join Joe Williams & Aleksandar Savkovic on 29th of March, 2021.

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Do you like what you read?

Get the Latest Updates

Share Your Feedback

Please insert Content

Thank you for your feedback!

Want to Experience the Cloudways Platform in Its Full Glory?

Take a FREE guided tour of Cloudways and see for yourself how easily you can manage your server & apps on the leading cloud-hosting platform.

Start my tour

CYBER WEEK SAVINGS

  • 0

    Days

  • 0

    Hours

  • 0

    Mints

  • 0

    Sec

GET OFFER

For 4 Months &
40 Free Migrations

For 4 Months &
40 Free Migrations

Upgrade Now