More fun visualizing MySQL Database Size

Hi again!

As a sidekick for my previous post, I came up with a snippet of code that generates the Google Chart URL to visualize table size for the current database. For example, for the sakila sample database, we get URL's like this:

http://chart.apis.google.com/chart?cht=bhs&chbh=19,2,2&chs=653x459&chtt=sakila%20Size%20(MB)&chco=4D89F9,C6D9FD&chd=t:0.0156,0.0156,0.0156,0.0156,0.0156,0.0469,0.0625,0.0625,0.0781,0.0781,0.1875,0.1875,0.1875,0.1163,0.1719,1.5156,1.5156|0.0000,0.0000,0.0000,0.0156,0.0313,0.0156,0.0156,0.0313,0.0156,0.0469,0.0781,0.0781,0.0781,0.2002,0.1875,0.6094,1.2031&chds=0,2.7188&chxt=y,x&chxl=0:|rental%20(InnoDB)|payment%20(InnoDB)|inventory%20(InnoDB)|film_text%20(MyISAM)|films%20(InnoDB)|film_actor%20(InnoDB)|film%20(InnoDB)|customer%20(InnoDB)|staff%20(InnoDB)|address%20(InnoDB)|film_category%20(InnoDB)|city%20(InnoDB)|store%20(InnoDB)|actor%20(InnoDB)|language%20(InnoDB)|country%20(InnoDB)|category%20(InnoDB)|1:|0|2.72MB&chm=N*f2*,000000,0,-1,11|N*f2*,000000,1,-1,11

The graph looks like this: Here's the script I used:

SET @maxpixels:=300000;
SET @barwidth:=19;
SET @spacebetweenbars:=2;
SET @spacebetweengroups:=2;
SET @totalbarwidth:=@barwidth+(2*@spacebetweenbars)+(2*@spacebetweengroups);
SET @megabytes:=1024*1024;
SET @decimals:=2;

SELECT      CONCAT(
                 'http://chart.apis.google.com/chart'
            ,    '?cht=bhs'
            ,    '&chbh=',@barwidth,',',@spacebetweenbars,',',@spacebetweengroups
            ,    '&chs=', @maxpixels div (COUNT(*) * @totalbarwidth),'x', COUNT(*) * @totalbarwidth
            ,    '&chtt=', table_schema, ' Size (MB)' 
            ,    '&chco=4D89F9,C6D9FD'
            ,    '&chd=t:',    GROUP_CONCAT(data_length / @megabytes ORDER BY (data_length+index_length))
            ,    '|', GROUP_CONCAT(index_length / @megabytes ORDER BY (data_length+index_length))
            ,    '&chds=' ,0, ',', MAX(data_length+index_length)/@megabytes
            ,    '&chxt=y,x'
            ,    '&chxl=0:|', GROUP_CONCAT(table_name, ' (', engine,')' ORDER BY (data_length + index_length) DESC separator '|')
            ,         '|1:|', 0, '|', ROUND(MAX(data_length+index_length) / @megabytes, @decimals), 'MB'
            ,    '&chm=N*f',@decimals,'*,000000,0,-1,11|N*f',@decimals,'*,000000,1,-1,11'
            )
FROM        information_schema.tables
WHERE       table_schema = SCHEMA()
AND         table_type   = 'BASE TABLE'
GROUP BY    table_schema

I'm not really satisfied yet...I keep hitting limitations w/re to google charts. I built a little bit of logic that will ensure the resulting picture is within the upper limit of 300000 pixels. I just found out there is another limitation that says the chart height can't exceed 1000 pixels. I'm going to stop looking at this for a while, and maybe later on I will come up with some works-most-of-the-time kind of logic to control this.

Feel free to drop me a line if you have some ideas regarding this.