Statistics for SHOW TABLE
Posted: Thu Jan 31, 2008 1:10 am
hi
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'";
if(!$conn)
{
echo "Unable to connect to the Database";
}
else
{
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 />";
}
}
==================================================================
This code works fine and i am able to display the values. following
are my questions.
1.
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= $array
['Data_length'];
$availabledata= $maxdata - $datalength;
echo "Data Available is = ". $availabledata;
By doing so $availabledata is not same as $maxdata - $datalength;
how to fix this.
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
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
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);
and use $result = mysql_query($showquery); equivalent to the total
number of tables from both the databases
please advice.
thanks a lot.
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'";
if(!$conn)
{
echo "Unable to connect to the Database";
}
else
{
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 />";
}
}
==================================================================
This code works fine and i am able to display the values. following
are my questions.
1.
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= $array
['Data_length'];
$availabledata= $maxdata - $datalength;
echo "Data Available is = ". $availabledata;
By doing so $availabledata is not same as $maxdata - $datalength;
how to fix this.
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
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
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);
and use $result = mysql_query($showquery); equivalent to the total
number of tables from both the databases
please advice.
thanks a lot.