yumupgrades for production use, this is the repository for you.
Active subscription is required.
To make the story short, you likely don’t need the data that bloats your Magento 2 database.
Below will review the tables which are usually grown without control, and how to keep them at bay for performance reasons.
More often than not, a huge Magento database will not scale in terms of performance.
Even MySQL has indexes to speed up queries against the big data, you will surely have a plugin (or even core code), which will make a bad query that doesn’t involve index use.
So essentially, a big Magento 2 database is evil unless you make it perfectly bug-free…
Which as we know is impossible, because Magento 2 is a huge trash bin of bugs.
Closed tickets on GitHub do not equal to solved issues. You will find many issues which are prematurely closed.
Locating the huge tables
Find largest tables in all databases
SELECT table_schema as "Database", table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE (data_length + index_length) > 1000000 ORDER BY table_schema, (data_length + index_length) DESC
This lists tables from all databases, which exceed 1 MB, sorted by size.
Find largest tables in a specific database
If you’re interested in a specific database only, you can run:
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE (data_length + index_length) > 1000000 AND table_schema = "magento" ORDER BY (data_length + index_length) DESC;
magento is your Magento database name,
This table is known to give performance problems. There is no cleaning done against it, so it will grow and grow during the lifetime of your website.
How to clean it? Use a monthly cron with
@monthly n98-magerun2 db:maintain:search-query --root-dir=/path/to/magento2
This will keep the related functions functioning while getting rid of irrelevant data (search queries that produce zero results and made more than a month ago).
Why this table being huge is a performance issue? Any time you update a product in admin, all rows in this table are reset to have
is_processed set to
The more rows to update, the worse. It can take minutes to update a product, if you’ve never cleaned this table.