Researching your MySQL table sizes

I posted a simple INFORMATION_SCHEMA query to find largest MySQL tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes:

Find total number of tables, rows, total data in index size for given MySQL Instance

Find the same data using some filter
I often use similar queries to find space used by particular table “type” in sharded environment when multiple tables with same structure and similar name exists:

Find biggest databases

Data Distribution by Storage Engines
You can change this query a bit and get most popular storage engines by number of tables or number of rows instead of data stored.

Trivial but handy.

Share this post

Comments (29)

  • Artem Russakovskii Reply

    Some very handy queries here, Peter. Thank you. This is going to my bookmarks.

    March 17, 2008 at 11:06 pm
  • Propiedad Privada » Blog Archive » Consultas MySQL útiles Reply

    […] Via MySQL Performance Blog […]

    March 18, 2008 at 3:05 am
  • ryan Reply

    These have defiantly gone into my snippets list, thanks.

    Also, BTW…
    Your syntax highlighter has a spelling error. When you switch to ‘PLAIN TEXT’, the option to switch back to the highlighted version is tagged ‘HILITED HTML’ – ought to be ‘HIGHLIGHTED HTML’.

    March 18, 2008 at 5:09 am
  • Artem Russakovskii Reply

    ryan, that’s just an American slang version of the word, not really a misspelling.

    March 18, 2008 at 7:18 am
  • ryan Reply

    Hum, learned two things today then 🙂

    March 18, 2008 at 1:36 pm
  • Prashanth Gajra Reply

    Anybody can let me know how can i get the total number of tables in database without using a SELECT query but by using a stored procedure?

    March 27, 2008 at 11:19 pm
  • peter Reply

    Well what is the problem with select query ?
    You can wrap it around by stored procedure if you need to.

    March 28, 2008 at 2:48 pm
  • Intenta » Blog Archive » Consultas útiles para MySQL Reply

    […] MySQL Performance blog he encontrado algunas consultas MySQL muy útiles a la hora de controlar el tamaño y […]

    May 14, 2008 at 7:52 am
  • Tamaño de tablas en MySQL Reply

    […] Vía: MySQL Performance Blog […]

    May 14, 2008 at 7:05 pm
  • daro » Blog Archive » Consultas útiles para MySQL Reply

    […] MySQL Performance blog he encontrado algunas consultas MySQL muy útiles a la hora de controlar el tamaño y […]

    June 8, 2008 at 3:08 pm
  • Consultas útiles para MySQL Reply

    […] el artículo Researching your mysql table sizes se explica como controlar el tamaño de nuestras tablas e indices, son consultas bastante útiles […]

    July 24, 2008 at 12:43 pm
  • Zeck’s Blog » Archive du blog » MySQL, quelques requêtes pour l’administration… Reply

    […] Researching your MySQL table sizes […]

    August 8, 2008 at 3:05 am
  • The ArcterJournal Reply

    Finding MySQL Table Disk Usage Via SQL…

    A little snippet that’s a deep-geek note-to-self. Darren found this from modifying information on this site and I figured I wanted to keep it around as it looks potentially handy to have. Also once google indexes things I’ll be able……

    August 20, 2008 at 2:33 pm
  • Alexey Kupershtokh Reply

    Could you remove all the “->” in order to allow people copy-paste right from here?

    September 12, 2008 at 8:20 am
  • Neil Reply

    Why does SUM(table_rows) fluctuate so wildly? I run this query:

    SELECT CONCAT(SUM(table_rows)/1000000,’M’) AS no_rows FROM information_schema.TABLES WHERE TABLE_SCHEMA = ”;

    and no_rows was first 2.73M, then 2.83M, then 2.81M, then 2.78M, then 2.81M…?

    Why does this vary?

    November 6, 2008 at 10:05 am
  • Artem Russakovskii Reply

    It’s a restriction of InnoDB. See http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html. It gives an approximate count, which fluctuates a lot.

    November 6, 2008 at 10:37 am
  • How Percona does a MySQL Performance Audit | MySQL Performance Blog Reply

    […] the server and see what I think about it. If the server is not heavily loaded, I may even do some INFORMATION_SCHEMA queries to help me find the biggest tables and so forth. If the server is heavily loaded or has a lot of data, touching the INFORMATION_SCHEMA […]

    November 24, 2008 at 9:42 pm
  • How Percona does a MySQL Performance Audit | Reflexiones IT Reply

    […] the server and see what I think about it. If the server is not heavily loaded, I may even do some INFORMATION_SCHEMA queries to help me find the biggest tables and so forth. If the server is heavily loaded or has a lot of data, touching the INFORMATION_SCHEMA […]

    November 28, 2008 at 12:47 am
  • Pereyrada » Consultas útiles en SQL Reply

    […] Web] MySQL Performance Blog Saludos A. Daniel […]

    December 1, 2008 at 3:20 pm
  • Taha Patel Reply

    This is really cool. Thanks.

    January 28, 2009 at 9:14 am
  • My daily readings 07/10/2009 « Strange Kite Reply

    […] Researching your MySQL table sizes | MySQL Performance Blog […]

    July 10, 2009 at 4:32 am
  • Gerry Reply

    Another vote for putting the queries on one line for copy and pasting.

    July 24, 2009 at 2:56 pm
  • bernard Reply

    excellent post, but one question. How to find total no. of indexes/index keys (not index size) and unique keys, ordered keys.I know you have the solution, please post it. Thanks in advance.

    January 19, 2010 at 3:59 am
  • kaushik katari Reply

    Easier to copy paste:

    SELECT count(*) TABLES, concat(round(sum(table_rows)/1000000,2),’M’) rows, concat(round(sum(data_length)/(1024*1024*1024),2),’G’) DATA, concat(round(sum(index_length)/(1024*1024*1024),2),’G’) idx,
    concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G’) total_size, round(sum(index_length)/sum(data_length),2) idxfrac
    FROM information_schema.TABLES;

    April 2, 2010 at 6:13 am
  • Kees Schepers Reply

    Nice article! Really good to analyse data usage!

    January 13, 2011 at 6:49 am
  • narendra Reply

    how to find only size of the table…?

    November 4, 2011 at 1:09 am
  • Preethi Reply

    Hi,

    I wanna know how much size an index will occupy for a newly created data.There is no data yet,jus wanna estimate how many bytes the index will occupy depending on the data size.

    For eg,

    create table t
    (name varchar(50),
    place_id int(10),
    lastdt_modifed timestmap) egine=innodb utf8;

    for calculating approximate data size i use the formula :

    (max row size * collation length(bytes)) * number of rows)

    (i.e)–> ((61*4)*100)

    {
    max row size : name = (50+3) , place_id = ( 4) , lastdt_modified = (4) : Total = 61
    For 100 rows = 24400 – > 24 KB
    }

    the size of one row calculated according to the above data is 61 Kb and for 100 rows it is 24400 Kb.

    How do i calculate the index size for this table (per row as well as entire table).

    Thanks.

    June 28, 2012 at 2:11 am
  • Axel Roth Reply

    Thanks for this handy mysql snippet.

    November 26, 2012 at 2:04 am
  • Ananth Kollipara Reply

    Hi…Does MySQL support something similar to what we have in ttSize/TimesTen, which helps in estimating avg. record size with 10%, 20%,…..90%, 100% occupancy?

    ttSize reference: http://gerardnico.com/wiki/timesten/ttsize#documentationreference

    August 24, 2016 at 3:28 am

Leave a Reply