Re: Statistics for SHOW TABLE

On Wed, 30 Jan 2008 05:02:31 +0100, Sudhakar <sudhakararaog@gmail.com>
wrote:
> my question is about "SHOW TABLE STATUS LIKE 'tablename'";

Which is MySQL, crossposted, f'upped to there.

> 1.
> echo "The number of allocated but unused bytes: ".$array['Data_free'];
> = This does not display any number at all.

http://bugs.mysql.com/bug.php?id=32440

> 2.
> echo "The maximum length of the data file. This is the total number of
> bytes of data that can be stored in the table : " .
> $array['Max_data_length'];
>
> displays 4294967295 which comes to 3.99 GB
>
> Does this mean that the Entire capacity for the MySql Database that is
> installed on the unix server including all the
> a) Tables and from all the Databases is 3.99 GB
>
> Because i have used this SHOW TABLE STATUS LIKE 'tablename' for all
> the tables i have have in 3 databases that i have, in each case the
> value displayed for
> echo $array['Max_data_length']; is 4294967295 which is 3.99 GB so i am
> assuming the total capacity for "MySql Database VERSION 3.23.58" that
> is installed on the unix server can hold a maximum of 3.99 GB of data
> each time data is inserted into the various tables which are in
> different databases

I'll leave this for more MySQL savvy users at comp.databases.mysql..

> 3.
> echo $array['Data_length'] = The value displayed is ex= 36872 for
> this, does it mean that 36872 number of bytes have been consumed by a
> particular table at a particular time, everytime an insert statement
> had been executed

No idea what this means.

> 4.
> Also i have 2 databases and each have couple of tables so i guess i
> will have to use the following statement 2 times
>
> mysql_select_db("database1", $conn);
> mysql_select_db("database2", $conn);

Or just mysql_query('USE database2'); will do the trick.
--
Rik Wasmus

On Fri, 01 Feb 2008 17:07:20 +0100, Rik Wasmus wrote:
> On Wed, 30 Jan 2008 05:02:31 +0100, Sudhakar <sudhakararaog@gmail.com>
> wrote:
>> my question is about "SHOW TABLE STATUS LIKE 'tablename'";
>
> Which is MySQL, crossposted, f'upped to there.
>
>> 1.
>> echo "The number of allocated but unused bytes: ".$array['Data_free'];
>> = This does not display any number at all.
>
> http://bugs.mysql.com/bug.php?id=32440
>
>> 2.
>> echo "The maximum length of the data file. This is the total number of
>> bytes of data that can be stored in the table : " .
>> $array['Max_data_length'];
>>
>> displays 4294967295 which comes to 3.99 GB
>>
>> Does this mean that the Entire capacity for the MySql Database that is
>> installed on the unix server including all the
>> a) Tables and from all the Databases is 3.99 GB
>>
>> Because i have used this SHOW TABLE STATUS LIKE 'tablename' for all
>> the tables i have have in 3 databases that i have, in each case the
>> value displayed for
>> echo $array['Max_data_length']; is 4294967295 which is 3.99 GB so i am
>> assuming the total capacity for "MySql Database VERSION 3.23.58" that
>> is installed on the unix server can hold a maximum of 3.99 GB of data
>> each time data is inserted into the various tables which are in
>> different databases
>
> I'll leave this for more MySQL savvy users at comp.databases.mysql..

By possibly faulty recollection, MyISAM tables are limited to whatever
the largest size file is supported by the OS/filesystem. If the disk
supports 4 GB files, then a MyISAM table holds 4 GB. Obviously, this
maximum then varies from platform to platform. I believe InnoDB uses
some voodoo aggregate storage scheme which puts data in multiple files
for all InnoDB data and therefore tends to be limited by the size of the
volume it's stored on. It's a "You shouldn't need to know this" kind of
situation.

>> 3.
>> echo $array['Data_length'] = The value displayed is ex= 36872 for
>> this, does it mean that 36872 number of bytes have been consumed by a
>> particular table at a particular time, everytime an insert statement
>> had been executed
>
> No idea what this means.

Nor I. But I'm not finding out from this what the actual problem being
solved is either. (:

>> 4.
>> Also i have 2 databases and each have couple of tables so i guess i
>> will have to use the following statement 2 times
>>
>> mysql_select_db("database1", $conn);
>> mysql_select_db("database2", $conn);
>
> Or just mysql_query('USE database2'); will do the trick.

Often it's worth using a database handler for each database you're
hooking up to, and switching handlers instead of switching databases
within an open connection. You're very unlikely to be grabbing the wrong
table then at the application level. (Trying to use a database
dynamically, without predetermining which database at coding time, ruins
this kind of trick, but I'd seriously recommend *not* trying to deal
with multiple, dynamically-assigned database. That's a short road to the
lunatic asylum, IME; it'll be very, very difficult to predict where
you're putting things unless you're assigning a USE statement in front
of pretty much every SQL command...)

--
52. I will hire a team of board-certified architects and surveyors to
examine my castle and inform me of any secret passages and abandoned
tunnels that I might not know about.
--Peter Anspach's list of things to do as an Evil Overlord