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

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

    March 17, 2008 at 11:06 pm
  • ryan

    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

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

    March 18, 2008 at 7:18 am
  • ryan

    Hum, learned two things today then 🙂

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

    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

    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
  • Alexey Kupershtokh

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

    September 12, 2008 at 8:20 am
  • Neil

    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

    It’s a restriction of InnoDB. See It gives an approximate count, which fluctuates a lot.

    November 6, 2008 at 10:37 am
  • Taha Patel

    This is really cool. Thanks.

    January 28, 2009 at 9:14 am
  • Gerry

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

    July 24, 2009 at 2:56 pm
  • bernard

    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

    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

    Nice article! Really good to analyse data usage!

    January 13, 2011 at 6:49 am
  • narendra

    how to find only size of the table…?

    November 4, 2011 at 1:09 am
  • Preethi


    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).


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

    Thanks for this handy mysql snippet.

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

    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:

    August 24, 2016 at 3:28 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.