Statistics for SHOW TABLE

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

my question is about "SHOW TABLE STATUS LIKE 'tablename'";

following is the code i am presently using


$conn = mysql_connect("localhost","username","password");

$showquery = "SHOW TABLE STATUS LIKE 'tablename'";

echo "Unable to connect to the Database";

mysql_select_db("databasename", $conn);

$result = mysql_query($showquery);

while($array = mysql_fetch_array($result))
echo "<br><br>
Table Name : ".$array['Name']."<br />
When the data file was last updated. : ".$array['Update_time']."<br /
Quoted text here. Click to load it



This code works fine and i am able to display the values. following
are my questions.

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

so i have subtracted the value of Max_data_length with Data_length
$maxdata=$array['Max_data_length']; $datalength=

$availabledata= $maxdata - $datalength;

echo "Data Available is = ". $availabledata;

By doing so $availabledata is not same as $maxdata - $datalength;

how to fix this.

echo "The maximum length of the data file. This is the total number of
bytes of data that can be stored in the table :  " .

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

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

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

and use $result = mysql_query($showquery); equivalent to the total
number of tables from both the databases

please advice.

thanks a lot.

Re: Statistics for SHOW TABLE

Greetings, Sudhakar.
In reply to Your message dated Wednesday, January 30, 2008, 07:02:31,

Quoted text here. Click to load it

What is Your PHP question, please?


Re: Statistics for SHOW TABLE

Quoted text here. Click to load it

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

Quoted text here. Click to load it

Quoted text here. Click to load it

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

Quoted text here. Click to load it

No idea what this means.

Quoted text here. Click to load it

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

Rik Wasmus

Re: Statistics for SHOW TABLE

On Fri, 01 Feb 2008 17:07:20 +0100, Rik Wasmus wrote:
Quoted text here. Click to load it

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

Quoted text here. Click to load it

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

Quoted text here. Click to load it

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

Site Timeline