{"id":24580,"date":"2025-12-06T15:00:00","date_gmt":"2025-12-06T09:30:00","guid":{"rendered":"https:\/\/meetanshi.com\/blog\/?p=24580"},"modified":"2025-12-10T12:33:25","modified_gmt":"2025-12-10T07:03:25","slug":"magento-2-database-optimization-steps","status":"publish","type":"post","link":"https:\/\/meetanshi.com\/blog\/magento-2-database-optimization-steps\/","title":{"rendered":"Magento 2 Database Optimization Steps You Must Implement\u00a0"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">A slow Magento 2 database directly impacts your store&#8217;s performance and, ultimately, your shoppers&#8217; experience.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">But, no more.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This blog is our Magento expert-approved list of all the important Magento 2 database optimization steps you can take today to speed up your store and stabilize checkout in 2026.\u00a0<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">5+ Magento 2 Database Optimization Steps (+ How Fix)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The database is the core of your Magento 2 store, preserving all product, customer, and order data and different configurations. Over time, the database gets bloated with unnecessary logs and inefficient indexing, directly resulting in slow loading times, poor admin performance, and even costly server downtime.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here are the exact steps you must follow to improve speeds by up to 2x, and ensure your store remains fast and stable even as your sales volume grows.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Remove Unnecessary Entries&nbsp;<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Magento stores various background data (like product and inventory updates) internally. This data can build up and slow down your store. The best way to avoid this is to leverage Magento&#8217;s automatic cleanup feature to remove old or unused data, which keeps the database fast.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here\u2019s the process to do it directly from the admin panel:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Head to <strong>Stores &gt; Configuration<\/strong> under Settings.&nbsp;<\/li>\n\n\n\n<li>Expand <strong>Advanced<\/strong> and select <strong>System<\/strong> in the left sidebar.<\/li>\n\n\n\n<li>Scroll to the <strong>MySQL Message Queue Cleanup.<\/strong><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Add the relevant values for the fields, and click <strong>Save Config<\/strong> to apply the changes.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"451\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/mysql-message-queue-cleanup-1024x451.png\" alt=\"Use MySQL message queue cleanup for removing unnecessary data\" class=\"wp-image-24581\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/mysql-message-queue-cleanup-1024x451.png 1024w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/mysql-message-queue-cleanup-300x132.png 300w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/mysql-message-queue-cleanup-768x338.png 768w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/mysql-message-queue-cleanup.png 1041w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Step 2: Set an Automatic Log Clearing<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You can manage and clear logs in Magento 2 using the command line (CLI), the admin panel, or by manually clearing the database tables via a tool like phpMyAdmin.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Using the Command Line Interface is the best and fastest way to delete large log files (like system.log and exception.log) from the var\/log directory on your server. You can trigger the cleanup manually using the CLI.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To clean database logs instantly, run:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">php bin\/magento log:clean<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If you want to keep logs only for a specific number of days, use the &#8211;days option:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">php bin\/magento log:clean --days=1<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This command will remove all log data older than one day.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Magento creates log files (system.log, exception.log, debug.log) in the var\/log directory. These files can be safely deleted and will be automatically recreated as needed.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To do so, head to your Magento project&#8217;s root directory in your terminal or SSH client.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Run the following command to delete all files within the var\/log directory:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">rm -rf var\/log\/*<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This helps free up disk space and ensures that only fresh logs are stored going forward.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Set Indexers to &#8220;Update on Schedule\u201d<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Changing the index mode from &#8220;Update on Save&#8221; to &#8220;Update on Schedule&#8221; is a good practice primarily for resource management, especially on large systems with frequent data changes.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Whenever you make any changes in the product price, stock, or category, it immediately recalculates and updates the affected index tables. This involves heavy database write operations.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When you change from &#8220;Update on Save&#8221; to &#8220;Update on Schedule,&#8221; it simply flags the index as needing an update. The actual intensive recalculation of the index happens later, when a cron job runs.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To change an indexer&#8217;s mode, use the Admin Panel, go to <strong>System &gt; Tools &gt; Index Management<\/strong>, select the indexer, choose <strong>Update by Schedule<\/strong> from the Actions dropdown, and click Submit.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"789\" height=\"414\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/set-index-to-update-by-schedule.png\" alt=\"Set mode to update on schedule to optimize Magento 2 database\" class=\"wp-image-24582\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/set-index-to-update-by-schedule.png 789w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/set-index-to-update-by-schedule-300x157.png 300w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/set-index-to-update-by-schedule-768x403.png 768w\" sizes=\"auto, (max-width: 789px) 100vw, 789px\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Step 4: Use OpenSearch as the Primary Engine<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Enabling OpenSearch as the primary search engine for Magento 2 is mandatory for versions 2.4.x and newer, as it replaces the less efficient MySQL-based search. It provides faster, more accurate search results and reduces the load on your main database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Every time a shopper searches for something in your store, the default MySQL search forces the main database server to scan the larger product tables to get the results.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">OpenSearch uses inverted indexing technology. When a search happens, it immediately returns the product IDs without taxing the main database without disturbing the main database.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here\u2019s how to use it as a primary search engine.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Log in to your Magento Admin and go to <strong>Stores<\/strong> &gt; <strong>Configuration<\/strong>, in the left-hand menu, expand the <strong>Catalog <\/strong>section, and click <strong>Catalog Search<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Then, in the <strong>Search engine <\/strong>field, fill in all the details and click <strong>Test connection <\/strong>to verify that all the information is correct.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"865\" height=\"430\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/use-opensearch-as-primary-engine.png\" alt=\"Use OpenSearch as the Primary Engine\" class=\"wp-image-24584\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/use-opensearch-as-primary-engine.png 865w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/use-opensearch-as-primary-engine-300x149.png 300w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/use-opensearch-as-primary-engine-768x382.png 768w\" sizes=\"auto, (max-width: 865px) 100vw, 865px\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Step 5: Avoid Using Flat Catalogue&nbsp;<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">The main reason to avoid using Flat Catalogue is its compatibility issues with the latest Magento 2 versions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The flat catalog is deprecated by Magento, starting with version 2.3.0. It was an essential feature for Magento 1 and 2 stores, but now search engines like OpenSearch handle this functionality much better.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Continuing to use it can cause indexing issues, data inconsistency, and performance degradation. Here are the steps to disable it.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Go to <strong>Stores &gt; Configuration<\/strong>&gt; <strong>Catalog &gt; Storefront<\/strong> in the Magento admin panel. <\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Set Use Flat Catalog Category to No<\/li>\n\n\n\n<li>Set Use Flat Catalog Product to No <\/li>\n\n\n\n<li>Click Save config.<\/li>\n<\/ul>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"872\" height=\"145\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/disable-flat-catalog-1.png\" alt=\"Disable the flat catalog\" class=\"wp-image-24585\" srcset=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/disable-flat-catalog-1.png 872w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/disable-flat-catalog-1-300x50.png 300w, https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/12\/disable-flat-catalog-1-768x128.png 768w\" sizes=\"auto, (max-width: 872px) 100vw, 872px\" \/><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\">Step 6: Update Your MySQL Database&nbsp;&nbsp;<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Updating the MySQL database is good practice for smooth operation, whether you&#8217;re upgrading Magento, adding extensions, or applying security patches, and prevents errors or data conflicts.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The Magento 2 MySQL database update process usually involves three main steps: backing up your current database, applying system or module upgrades, and letting Magento automatically update the database using Command Line Interface (CLI) commands.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Most updates are handled using the following core command:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"generic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">bin\/magento setup:upgrade<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Once completed, Magento automatically updates the required database tables and structure.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">After updating the database, it\u2019s also essential to:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Run dependency compilation<\/li>\n\n\n\n<li>Deploy static content (for production mode)<\/li>\n\n\n\n<li>Flush and clean caches<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">These steps ensure that both the backend database and frontend store remain fully synchronized.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Tips to Avoid the Impact on Your Magento 2 Database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">These are some bonus tips that definitely block unnecessary stress on your Magento 2 DB.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Prioritize a Multi-Layer Caching Strategy&nbsp;<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Optimizing cache should be done at three levels: application, server, and network.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Application-Level Caching <\/strong>avoids cache flushes to maintain performance. Use Redis for Magento&#8217;s cache and sessions instead of the file system. This faster, in-memory store reduces DB load. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Full Page Caching <\/strong>prevent requests from reaching the Magento database. It stores and instantly serves fully rendered HTML pages. For optimal results, Varnish should be placed in front of your web server to handle requests.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Server-Level Caching <\/strong> should be configured on web servers like Nginx or Apache using proper cache headers, allowing browsers to store these files locally. Using a CDN further improves performance by delivering static content from the closest server.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Audit All Third-Party Extensions&nbsp;<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Regularly auditing extensions is needed because outdated or unnecessary modules can slow down your website and expose it to security risks, which is why you can audit your installed extensions in three simple phases.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Start by identifying all the modules installed on your store. You can use the following CLI command to get a complete list: <strong>php bin\/magento module:status<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once you have the list, review each extension and disable anything that is no longer required. Also, check the developer\u2019s reputation and the last update date of the extension. Extensions that haven\u2019t been updated in a long time have a higher security and performance risk. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you suspect an issue, disable the extension via CLI to confirm the problem. Keep a list of all installed extensions and their versions for easy future updates.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Separate Your Web and Database Servers<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Moving the database (MySQL\/MariaDB) to a separate server from the web application is a top hardware optimization for busy or growing Magento 2 stores. This separation greatly boosts both performance and security.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The process involves preparing the new database server and updating your Magento configuration to point to the new location.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Prepare the new database server<\/strong> for your database (MySQL\/MariaDB) and secure it by only allowing your web server&#8217;s IP to connect.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Migrate the database <\/strong>to the new database server, and import it. Then, create a new database user with the required permissions for Magento.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Update Magento configuration<\/strong> edit the php file and replace localhost with the new database server IP or hostname. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">After saving the changes, flush the cache and test your storefront to confirm everything works correctly.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Let Experts Manage This Task&nbsp;<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Attempting this solo setup is not a good idea. While detailed Magento 2 database optimization steps unlock the major performance gains, they come with a high risk. One wrong move, and you could face irreversible data loss or a site crash.&nbsp;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To ensure the safest optimization of your Magento store&#8217;s database, consider hiring a <a href=\"https:\/\/meetanshi.com\/magento-development-services.html\">Magento developer service<\/a>. A professional will maintain an up-to-date, properly configured database, apply the correct strategies, and help you avoid unnecessary complexity and hassle.<\/p>\n\n\n<div class=\"meetanshi-cta\">\r\n<div class=\"cta-content-wrapper\">\r\n<span>Magento Development Services<\/span>\r\n<p>We help you create a high-performing, scalable, and secure Magento 2 store.<\/p>\r\n<a href=\"https:\/\/meetanshi.com\/magento-development-services.html\" target=\"_blank\" class=\"btn-primary\">Talk with Us<\/a>\r\n<\/div>\r\n<div class=\"cta-image-new\">\r\n<img decoding=\"async\" src=\"https:\/\/meetanshi.com\/blog\/wp-content\/uploads\/2025\/11\/magento-development-services.png\" alt=\"Magento Development Services\">\r\n<\/div>\r\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>A slow Magento 2 database directly impacts your store&#8217;s performance and, ultimately, your shoppers&#8217; experience. But, no more. This blog is our Magento expert-approved list&#8230;<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[34],"tags":[],"class_list":["post-24580","post","type-post","status-publish","format-standard","hentry","category-magento"],"acf":[],"_links":{"self":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/24580","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/comments?post=24580"}],"version-history":[{"count":6,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/24580\/revisions"}],"predecessor-version":[{"id":24932,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/posts\/24580\/revisions\/24932"}],"wp:attachment":[{"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/media?parent=24580"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/categories?post=24580"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/meetanshi.com\/blog\/wp-json\/wp\/v2\/tags?post=24580"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}