Researching your MySQL table sizes

PREVIOUS POST
NEXT POST

I posted a simple INFORMATION_SCHEMA query to find largest 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.

PREVIOUS POST
NEXT POST

Comments

  1. says

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

  2. Prashanth Gajra says

    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?

  3. Neil says

    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?

  4. says

    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.

  5. says

    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;

  6. Preethi says

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *