MySQL can become a real issue with database queries taking too long when you lack indexes for the most popular entries or have an improperly configured DB server. Furthermore, it also affects the and Magento performance in general.
In this guide, we assume you already know your database is the real bottleneck of the system, so there's no need to investigate this issue further. Here we'll discuss actionable steps to optimize the Magento MySQL database.
Table of Content
The Role of Overall Magento Store Optimization
To see a significantly better store loading speed, Magento 2 database optimization should be a part of a broader optimization strategy. We recommend you read our , with a comprehensive list of improvements you can make for Magento cache, servers, media, and code.
And if you're ready to delve deeper into the matter, our other guides on and definitely come in handy. And without further ado, let's see how to diagnose and troubleshoot Magento database performance issues.
1. Remove Useless Entries From Magento Database
We've already mentioned this tip in our . This is a great solution for more than just Shopping Cart optimization. Truncating works well for bloated log entries, various system events, comparisons inside the catalog, and some other files you might not really care about.
Create a backup and then use the TRUNCATE command on 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,
- index_event,
- catalog_compare_item,
- catalogindex_aggregation,
- catalogindex_aggregation_tag,
- catalogindex_aggregation_to_tag,
- adminnotification_inbox,
- aw_core_logger.
In case you like to automate ALL THE THINGS, take a look at our speed optimization guide, where you can find step-by-step instructions on how to install and use a dedicated module that cleans Magento store logs via cron once a week.
2. Switch to a Magento MySQL Database Alternative
Traditional MySQL implementations might be the safest choice for your Magento install, but if you want to squeeze every last bit of juice out of your Magento database setup, look into switching to either MariaDB or Percona. But what's the difference between these 3 systems?
MySQL
In general, the biggest advantage of MySQL is its long-term endurance. MySQL entered the market in the 90s and has been in the leading positions ever since.
Magneto MySQL 8.0 boasts two times higher speed in comparison to its previous version supported by Magento. It also has improved compatibility, robust architecture, and huge help documentation. To list both pros and cons, MySQL, in some cases, requires a paid license for commercial use, offers limited capabilities in scaling and security, and generally rests on its laurels too much.
MySQL advantages for Magento:
With MySQL, one thing you need to understand well is expectations. MySQL is a great baseline solution for database management if you don't expect the store to handle extreme loads. Sure, it's a bit basic, but it gets the job done. You will always find tons of help online and knowledgeable teams ready to manage your database in a conservative but solid way.
MariaDB
MariaDB is more community-focused and works better for users who care more about features than pure performance. MariaDB supports a large array of database engines, disk encryption, complex horizontal interconnectivity, and scaling features which could be interesting for a large Magento store.
One thing to consider, though. MariaDB supports switching from MySQL to MariaDB, but not the other way around if you plan on using a complex replication schema with global transactional IDs. Migration is a one-way ticket for those who would like to make a move, so think twice. Backward compatibility could be important if you are just experimenting and not ready to commit to MariaDB in the long term.
Among prominent MariaDB adherents are Ansell, America Movil, BlaBlaCar, Nokia, Red Hat, and Samsung.
MariaDB advantages for Magento:
- Advanced Magento sharding support (Spider storage engine or Galera cluster);
- In-depth horizontal replication between multiple sources, intricate scaling, and connectivity;
- Performance boost compared to the default MySQL configuration (see for an in-depth comparison of different workloads).
Percona
Percona is a fork of MySQL that centers around performance and peak load handling. Compared to MariaDB, Percona is a less popular, more specialized database engine. Even though the two alternatives were released roughly the same time (2008-2009), they have very different adoption rates and community sizes.
Among high-profile Percona fans are Facebook, Netflix, SoundCloud, and Adobe.
Percona advantages for Magento:
- Better performance for large datasets and heavy loads especially using expensive enterprise-level hardware (thread pool scaling, 48-CPU scaling limit);
- Noticeable advantages for high-load eCommerce applications;
- Good security (encryption, advanced user isolation through sharding, audit logging, PAM authentication);
- Proper database performance optimization and diagnostics tools (query logging, I/O count, access counters, etc.).
MariaDB or Percona?
Choose MariaDB if you need more quality-of-life and DevOps features. Go for Percona if you aim to gain high-load performance in large-scale datasets.
NOTE: Comparing MySQL, Percona, and MariaDB, you would inevitably come to comparisons of XtraDB and InnoDB. Here's our take on the situation. XtraDB is only slightly better than InnoDB, which means you shouldn't worry too much about which engine you actually use. The gain in Magento performance is not worth the upgrade. You are better off investing in other optimization efforts.
3. DO NOT Switch to Flat Catalogs
Flat catalogs used to help reduce database load. It worked like this: flattening cuts down on DB queries since the system can fetch the same amount of data with fewer calls. In large-scale stores with vast product counts, these gains from flat catalogs and flat product categories were especially noticeable, but only up to Magento 2.1.x versions.
But if you run the site on newer editions, flat catalog usage becomes more of a problem. With flat tables and indexers enabled you can encounter "performance degradation and other indexing issues". So, according to official , the flat catalog feature must be turned off on Magento 2.1.x versions and higher (the type of Adobe Commerce edition doesn't matter).
In Magento 2 Admin Panel, go to: Stores > Configuration > Catalog. Make sure to switch to "NO" on both "Use Flat Catalog Category" and "Use Flat Catalog Product".
4. Turn on Logging to Find Poorly Performing Queries
MySQL offers built-in tools to facilitate performance optimization, one of which is a slow query log. Turn it on, define which kind of queries you consider too slow, and watch the log grow. All queries longer than a certain amount will end up here.
For example, let's start with populating the log with queries longer than 1 second. Once you deal with the slower ones, you can move on to queries longer than 500 ms, etc. To define which queries the system should consider slow, use the long_query_time value.
Use statement to find out the structure and contents of each slow query and properly analyze it.
5. Update Your Database Version
Sounds obvious, but newer is always better. Update your Magento database to the latest version to improve performance, security and fix bugs. New versions often boast better read and write I/O speeds, concurrency performance, improved algorithms, and other upgrades. Keeping your database up to date costs literally nothing.
As of July 2023, the latest supported version of MySQL is 8.0, and MariaDB is 10.6.
6. Add Indexes to Large Tables
Processing queries from larger MySQL tables can be extremely slow, up to 30-60 seconds in the worst cases. Add indexes to the tables to reduce the amount of time needed to process data. The bigger the table, the more visible the website performance gain. The only downside of total indexing is data storage.
Why don't extension developers add indexes to their tables? Most of the time, they either don't bother to add or forget to do that before release. So it's up to you to fix this issue. All tables will benefit from having an index.
7. Set Elasticsearch/OpenSearch as Your Search Engine
If your Magento is older than 2.4.x version, then it's worth switching from MySQL as a default search option to Elasticsearch or another engine. Elastic-powered search and layered navigation work faster than the default MySQL setup. Elasticsearch will lift some workload from your database and, at the same time, offer better performance, especially if you move it to a separate machine.
If your store is on Magento 2.4.x or a later version, then Elasticsearch/OpenSearch will be your built-in search engine. Learn more about how to in our comprehensive guide.
Another MySQL tuning tip is to turn off product count from layered navigation. This setting slows down page loading and doesn't bring a lot of value in return. Switching it off is standard practice in most optimization scenarios.
Magento version
Go to "Stores > Configuration > Catalog > Catalog. Locate "Layered Navigation" then untick "Use System Value" and set "Display Product Count" to "No".
8. Discover Hidden Issues Using Advanced Tools
is a well-known diagnostics script that helps developers find issues in their MySQL setups and fix them following the script's recommendations. It can uncover hidden issues that are otherwise hard to see.
If you have already implemented all our tips and looking for more action, run MySQLTuner and see what it can find. Alternatively, use the tool to analyze each database request and optimize them one by one. Amongst other advanced tools that could help you with database optimization, we can name which focuses on the following tables:
- Slow Query Log,
- Max Connections,
- Worker Threads,
- Memory Usage,
- Key Buffer,
- Query Cache,
- Sort Buffer,
- Joins,
- Temp Tables,
- Table (Open & Definition) Cache,
- Table Scans (read_buffer),
- Table Locking,
- InnoDB Status.
If you want to know more about further, we've created an in-depth guide to reveal all the secrets.
40 Hours of Magento Services. FOR FREE
Try our custom development, optimization, support, and design services. One week, free of charge, no strings attached.
Making Magento Database as Fast as Possible
Always keep in mind your return on investment. Sometimes it doesn't make a lot of business sense to invest hundreds of person-hours into honing and tweaking your Magento MySQL config. If you can gain the same website performance improvement from expanding your hardware setup, do it. Take into account both the time and cost of implementation when you make this business decision.
Throwing more money at the problem might not be the most elegant solution, but if it's the most cost-effective, it's a great alternative. Want to know how we deal with slow Magento database performance? With our , your store will be as swift as an arrow! Let's look at your website together and discuss how our team can speed it up.
