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 the 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, table_schema 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;
… where magento is your Magento database name,
Huge search_query table
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 db:maintain:search-query command:
@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 0.
The more rows to update, the worse. It can take minutes to update a product if you’ve never cleaned this table.
Huge mst_seo_audit_url table
This table belongs to the Seo Audit feature of the Magento 2 Advanced Seo Suite by Mirasvit.
On a live instance of one of the websites, this module generated 300 GB worth of data!
Disable the module for being of poor quality, and truncate the database table:
clearmage2 --disable Mirasvit_SeoAudit
n98-magerun2 db:query "TRUNCATE mst_seo_audit_url;"
# remove unused space
n98-magerun2 db:query "OPTIMIZE TABLE mst_seo_audit_url;"
# refresh statistics about size
n98-magerun2 db:query "ANALYZE TABLE mst_seo_audit_url:"