We have to be honest with you: database is rarely a bottleneck for Magento stores. This is because Magento has a lot of other narrow places that become bottlenecks much more often than MySQL. 

If you are not sure that the database is the actual bottleneck, we recommend you use extensive analysis to establish this first. Otherwise, even if you optimize your database, it won’t give you the desired effect.

If you are 100% sure the issue is with the DB, then let’s see how we can fix it. This article is a concise overview of DB optimization challenges and focuses on the most common solutions you can implement right away. 

Signs your DB is the real bottleneck of the store:

• database queries take too long to complete,
• you don’t have a specific database server config,
• your database has no indexes,
• Magento search is powered by MySQL and it’s slow.

Migrating your MySQL database is extremely important during Magento upgrade, so if you order Magento 2 migration service always make sure the company knows what they are doing.

1. Choose a Faster Database Solution

MySQL has one great thing about it: it’s the most popular choice. It’s also the safest, one of the most reliable and trusted solutions on the market. MySQL is a go-to option for architects that are concentrated on compatibility, robustness, and self-help troubleshooting documentation.

On the other hand, MySQL is slow to innovate, has multiple issues with scaling and security, and is not 100% free. In the case of Magento, MySQL is a decent solution but if it becomes a bottleneck you need to switch to a more powerful alternative.

Our recommendation: choose between MariaDB and Percona. Both offer more features than MySQL. MariaDB focuses more on security and functionality expansion. It’s a good choice when you want advanced Magento sharding, horizontal replication between two or more sources, and Galera clusters / Spider storage support.

Percona is loved for scale handling, unrivaled performance, and peak load optimizations. It’s ideal for huge datasets, extremely high CPU scaling, bulletproof user isolation with sharding, and advanced diagnostics tools.

In short, use MariaDB for smaller Magento stores. Percona is better when you need to manage a major store with enterprise-level loads and high complexity challenges.

2. Create DB Indexes for Key Tables

How many developers of third-party extensions actually remember to add indexes to their DB tables? Well, according to our experience, very few. Even though it’s a relatively easy fix, most development teams forget to add indexes during production. Why?

When they are testing the application, they do it in a clean, demo store environment where a single third-party extension doesn’t have any performance issues. But the actual users encounter a huge host of performance challenges when they try to do the same thing on their servers. First of all, there are already dozens different third-party extensions competing for server resources. Not a good start. Second, the performance lag becomes cumulative when you need to use multiple tables and create serious load with hundreds of DB queries.

This combination of high load and neglect to understand scaling challenges creates slow database queries and poor DB performance. Adding indexes to the DB can mitigate the performance drop and reduce the bottleneck.

Note: all tables will benefit from getting an index but the most noticeable improvement will be for the large ones. There are virtually no drawbacks to adding indexes to all tables. You shouldn’t really worry about resources unless your disk space is really tight. Even in this case it’s usually cheaper to expand the SSD space than to go without the indexes.

3. Use Flat Catalogs to Lighten Database Load

Magento fetches a lot of data with separate calls. All these calls create a high load on the database server. Flattening removes the necessity to call the DB so many times. It’s an efficiency measure that works best for large stores with thousands of products.

Generally speaking, the more products you have, the bigger is the difference in performance. Flattening affects both products and product categories (you’ll need to switch it on in Admin Panel. The settings are located in Stores > Configuration > Catalog, “Use Flat Catalog Category” and “Use Flat Catalog Product”.).

Note: flattened catalogs and categories offer better performance for all kinds of stores although the biggest boost will come from larger Magento stores that need to manage hundreds of thousands of products every day.

4. Switch from MySQL Search to Elasticsearch

This one is both a crucial optimization step and a great quality-of-life improvement. The thing is, Magento 2 uses a built-in MySQL-powered search to find stuff in your store. It works well enough for Magento stores with fewer products (let’s say 1,000-5,000 items on sale). When you get beyond this number, though, things slow down a notch.

Some of you might say that MySQL is generally not a bad choice of a search engine unless the store is really big. That’s true. The problem with MySQL is that Elasticsearch is so much better. It adds to the performance side of things all the small improvements to store search functionality that users generally lack when they have to use the default search.

Elasticsearch gets to shine when you have typos in your search queries, search too much, and so on. In general, if you see dips in database performance, consider switching to Elasticsearch just to remove some load from the DB server. The database will offload search-related MySQL queries to other places thus freeing up more power for actual important DB stuff.

This move kills two birds with one stone. You get a faster and more capable search and make your database server faster. What’s not to like?

5. Remove Product Count From Layered Navigation

This tip is standard practice in most first-time Magento optimization guides. If you somehow managed to overlook this setting, now is the time to fix it. Go to Stores > Configuration > Catalog > Catalog and change Layered Navigation to No for Display Product Count option.

See and Fix Specific Bottlenecks With Strong Tools

We’ve listed 5 most common fixes that will guarantee a boost to your database performance. Even though most stores will remove the majority of their performance challenges with these ready-to-use instructions, some more intricate optimizations are inevitably left out.

These are the challenges that are unique to every store. Use MySQLTuner or ProfileSQL to analyze DB performance and find out how to fix any performance issues. Alternatively, use Tuning Primer Script to quickly fix standard tables without in-depth knowledge of the intricacies of each table issues.

Lift Your Database Performance to a New Level

Working on database optimization don’t forget that you have to first identify if the DB is really your bottleneck. It’s quite rare to see a Magento store that gets bogged down with lack of DB capabilities. For Magento, the bottleneck often lies in CPU consumption or asset management. If your DB server is halfway decent (uses an SSD, hosts on Magento-focused hosting service) you are good.

Of course, even for a well-functioning DB you still need to do some homekeeping here and there, as well as monitor for slowdowns from time to time. Our message is that if you think it’s the database issue, most of the time it’s not the database.