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 :
- 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
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.
- On the left-hand side, under Advanced, click on System (Advanced > System).
- Under System, you see 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.
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.
Cloudways also offers integration with Elasticsearch for Magento store performance.
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.
On the right side of your MySQL manager, click on the below-mentioned boxes for the following tables:
- 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
- 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:
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.
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]
