query to return table and index size?

sneakyimp

is there a mysql query (v4 and higher) that can tell me how large my table and index are in bytes? Ideally, this query would return values for both index and table as separate, numeric values rather than as part of some large string.

NogDog

Looks like the SHOW TABLE STATUS command would give you that info (along with a bunch of other stuff).

sneakyimp

that's exactly what i'm after. i just need to figure out a little about the data returned now...and can somone tell me more about this:

Max_data_length
The maximum length of the data file. This is the total number of bytes of data that can be stored in the table, given the data pointer size used.

What are they referring to with 'data pointer size'?

sneakyimp

What does Max_data_length really mean? What influences this value? And why is this table with an average row length of 9 limited to a whopping 38,654,705,663 bytes:

CREATE TABLE `test_simple_assoc` (
  `id` int(12) unsigned NOT NULL auto_increment,
  `f_id` int(12) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=100000001 ;

while this one with an avg row length of 48 limited to only 4,294,967,295

CREATE TABLE `test_zip_assoc` (
  `id` int(12) unsigned NOT NULL auto_increment,
  `f_id` int(12) unsigned NOT NULL default '0',
  `zip` varchar(5) NOT NULL default '',
  `lat_radians` decimal(12,11) NOT NULL default '0.00000000000',
  `long_radians` decimal(12,11) NOT NULL default '0.00000000000',
  PRIMARY KEY  (`id`),
  KEY `lat_radians` (`lat_radians`,`long_radians`)
) TYPE=MyISAM AUTO_INCREMENT=10000001 ;