A Magento database can grow large over time. Before you investigate or delete individual tables, identify which tables consume the most space.
The following SQL query lists the 20 largest tables, including data size, index size and total size:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT TABLE_NAME AS Table_Name, TABLE_ROWS AS Rows_Estimated, ROUND(DATA_LENGTH / 1024 / 1024, 2) AS Data_MB, ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS Index_MB, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS Total_MB, ROUND(DATA_LENGTH / 1024 / 1024 / 1024, 3) AS Data_GB, ROUND(INDEX_LENGTH / 1024 / 1024 / 1024, 3) AS Index_GB, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 3) AS Total_GB FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'YOUR_MAGENTO_DATABASE' ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC LIMIT 20; |
YOUR_MAGENTO_DATABASE Replace this with the actual name of your Magento database. You can find it, for example, in app/etc/env.php under dbname.
The Total_MB and Total_GB are particularly useful because it includes data and indexes. This helps spot unusually large cron, quote, log or extension tables.
The column Rows_Estimated should be treated only as an approximate value for InnoDB. For locating especially large tables, DATA_LENGTH and INDEX_LENGTH are more important.
The query does not change any data and is a useful first check before investigating why your Magento database has grown so large.