Introduction
Magento is a giant e-commerce application having more than 300 tables. It uses an eav model concept and provides different complex features which makes the database huge.
Sometimes you may wonder what’s the database size of your Magento database or individual tables so that you can work on some optimization task or freeing some of your server space.
Here we are going to discuss some SQL queries which seems to be helpful.
SQL Snippets
1. Find size & rows of Magento database
SELECT
TABLE_SCHEMA AS "Database",
SUM(TABLE_ROWS) AS "Rows #",
ROUND(
SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
2
) AS "Size (MB)"
FROM
information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name'
GROUP BY TABLE_SCHEMA;
2. Find size & rows of Magento database tables
SELECT
TABLE_NAME AS "Table",
TABLE_ROWS AS "Rows #",
ROUND(
(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
2
) AS "Size (MB)"
FROM
information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name'
3. Find size & rows of Magento log tables
SELECT
TABLE_NAME AS "Table",
TABLE_ROWS AS "Rows #",
ROUND(
(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024,
2
) AS "Size (MB)"
FROM
information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA = 'database-name'
AND (
TABLE_NAME LIKE 'log_%'
OR TABLE_NAME LIKE 'report_%'
OR TABLE_NAME LIKE 'dataflow_%'
OR TABLE_NAME = 'catalog_compare_item'
)
ORDER BY TABLE_ROWS DESC
Magento log tables info
These queries can be useful for optimizing your database. Suppose say if log tables are huge with large no of data then you can optimize the log tables by truncating them using following SQL:
TRUNCATE dataflow_batch_export; TRUNCATE dataflow_batch_import; TRUNCATE log_customer; TRUNCATE log_quote; TRUNCATE log_summary; TRUNCATE log_summary_type; TRUNCATE log_url; TRUNCATE log_url_info; TRUNCATE log_visitor; TRUNCATE log_visitor_info; TRUNCATE log_visitor_online; TRUNCATE report_viewed_product_index; TRUNCATE report_compared_product_index; TRUNCATE report_event; TRUNCATE index_event; TRUNCATE catalog_compare_item;
Caution: Always take a DB backup before performing truncate operation.
Hope you found this article useful.
Thanks for reading!
