How to Find the size/rows of Magento Database & Tables?

Introduction

Magento is a giant e-commerce application having more than 300 tables. It uses an EAV model concept and provides different complex features that make 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 

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 the 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 the truncate operation.

Hope you found this article useful.
Thanks for reading!