How to get the sizes of the tables of a MySQL database

Question

I can run this query to get the sizes of all tables in a MySQL database   show table status from myDatabaseName    I would like some help in understanding the results  I am looking for tables with the largest sizes    Which column should I look at

User · Answer

SELECT      table name AS  Table         round   data length   index length    1024   1024   2  as size    FROM information schema TABLES   WHERE table schema    YOUR DATABASE NAME    ORDER BY size DESC     This sorts the sizes  DB Size in MB

User · Answer

If you have ssh access  you might want to simply try du -hc  var lib mysql  or different datadir  as set in your my cnf  as well

User · Answer

Another way of showing the number of rows and space occupied and ordering by it     SELECT      table schema as  Database        table name AS  Table        table rows AS  Quant of Rows        round   data length   index length    1024   1024  1024   2   Size in GB  FROM information schema TABLES WHERE table schema    yourDatabaseName  ORDER BY  data length   index length  DESC      The only string you have to substitute in this query is  yourDatabaseName

User · Answer

You can use this query to show the size of a table  although you need to substitute the variables first    SELECT      table name AS  Table        round   data length   index length    1024   1024   2   Size in MB   FROM information schema TABLES  WHERE table schema     DB NAME      AND table name     TABLE NAME     or this query to list the size of every table in every database  largest first   SELECT       table schema as  Database         table name AS  Table         round   data length   index length    1024   1024   2   Size in MB   FROM information schema TABLES  ORDER BY  data length   index length  DESC

User · Answer

Size of all tables   Suppose your database or TABLE SCHEMA name is  news alert   Then this query will show the size of all tables in the database   SELECT   TABLE NAME AS  Table     ROUND   DATA LENGTH   INDEX LENGTH    1024   1024  2  AS  Size  MB   FROM   information schema TABLES WHERE   TABLE SCHEMA    news alert  ORDER BY    DATA LENGTH   INDEX LENGTH  DESC    Output        --------- -----------        Table     Size  MB         --------- -----------        news           0 08         keyword        0 02        --------- -----------      2 rows in set  0 00 sec   For the specific table   Suppose your TABLE NAME is  news   Then SQL query will be-  SELECT   TABLE NAME AS  Table     ROUND   DATA LENGTH   INDEX LENGTH    1024   1024  2  AS  Size  MB   FROM   information schema TABLES WHERE     TABLE SCHEMA    news alert    AND     TABLE NAME    news  ORDER BY    DATA LENGTH   INDEX LENGTH  DESC    Output    ------- -----------    Table   Size  MB     ------- -----------    news         0 08    ------- -----------  1 row in set  0 00 sec

User · Answer

select x dbname as db name  x table name as table name  x bytesize as the size from    select      table schema as dbname       sum index length data length  as bytesize       table name    from      information schema tables    group by table schema     x where   x bytesize  gt  999999 order by x bytesize desc

User · Answer

If you are using phpmyadmin then just go to the table structure  e g   Space usage Data    1 5 MiB Index   0   B Total   1 5 Mi

User · Answer

Try the following shell command  replace DB NAME with your database name       mysql -uroot  lt  lt  lt  SELECT table name AS  Tables   round   data length   index length    1024   1024   2   Size in MB  FROM information schema TABLES WHERE table schema     DB NAME   ORDER BY  data length   index length  DESC     head   For Drupal drush solution  check the following example script which will display the biggest tables in use      bin sh DB NAME   drush status --fields db-name --field-labels 0   tr -d   r n    drush sqlq  SELECT table name AS  Tables   round   data length   index length    1024   1024   2   Size in MB  FROM information schema TABLES WHERE table schema       DB NAME    ORDER BY  data length   index length  DESC     head -n20

User · Answer

Heres another way of working this out from using the bash command line     for i in mysql -NB -e  show databases   do echo  i  mysql -e  SELECT table name AS  Tables   round   data length index length  1024 1024  2   Size in MB  FROM information schema TABLES WHERE table schema     i   ORDER BY  data length   index length  DESC    done

User · Answer

There is an easy way to get many informations using Workbench    Right-click the schema name and click  Schema inspector   In the resulting window you have a number of tabs  The first tab  Info  shows a rough estimate of the database size in MB  The second tab   Tables   shows Data length and other details for each table

User · Answer

This should be tested in mysql  not postgresql   SELECT table schema     DB Name    Round Sum data length   index length    1024   1024  1     DB Size in MB   FROM   information schema tables  GROUP  BY table schema

User · Answer

If you want a query to use currently selected database  simply copy paste this query   No modification required   SELECT table name     round   data length   index length    1024   1024   2  as SIZE MB FROM information schema TABLES WHERE table schema   DATABASE   ORDER BY SIZE MB DESC

User · Answer

I find the existing answers don t actually give the size of tables on the disk  which is more helpful   This query gives more accurate disk estimate compared to table size based on data length    amp  index  I had to use this for an AWS RDS instance where you cannot physically examine the disk and check file sizes   select NAME as TABLENAME FILE SIZE  1024 1024 1024  as ACTUAL FILE SIZE GB   round   data length   index length    1024   1024 1024   2  as REPORTED TABLE SIZE GB  from INFORMATION SCHEMA INNODB SYS TABLESPACES s join INFORMATION SCHEMA TABLES t  on NAME   Concat table schema     table name  order by FILE SIZE desc

User · Answer

SELECT TABLE NAME AS table name   table rows AS QuantofRows   ROUND  data length   index length   1024  2   AS total size kb  FROM information schema TABLES WHERE information schema TABLES table schema    db  ORDER BY  data length   index length  DESC     all 2 above is tested on mysql

User · Answer

Adapted from ChapMic s answer to suite my particular need   Only specify your database name  then sort all the tables in descending order - from LARGEST to SMALLEST table inside selected database  Needs only 1 variable to be replaced   your database name   SELECT  table name AS  Table    round   data length   index length    1024   1024   2  AS  size  FROM information schema TABLES  WHERE table schema    YOUR DATABASE NAME HERE  ORDER BY size DESC

User · Answer

SELECT TABLE NAME AS  Table Name    table rows AS  Quant of Rows   ROUND    data length   index length    1024  2   AS  Total Size Kb  FROM information schema TABLES WHERE information schema TABLES table schema    YOUR SCHEMA NAME DATABASE NAME HERE  LIMIT 0   30   You can get schema name from  information schema  -  SCHEMATA table -   SCHEMA NAME  column    Additional You can get size of the mysql databases as following   SELECT table schema  DB Name    Round Sum data length   index length    1024   1024  1   DB Size in MB   FROM   information schema tables  GROUP  BY table schema     Result  DB Name                     DB Size in MB  mydatabase wrdp             39 1 information schema          0 0   You can get additional details in here

User · Answer

Calculate the total size of the database at the end    SELECT    table name AS  Table      round   data length   index length    1024   1024   2   Size in MB     FROM information schema TABLES    WHERE table schema     DB NAME    UNION ALL  SELECT     TOTAL      SUM round   data length   index length    1024   1024   2      FROM information schema TABLES    WHERE table schema     DB NAME

[mysql] How to get the sizes of the tables of a MySQL database?

Examples related to mysql