Database Size

[ash11]

Hi,

In my database i have users and there stuffs..
I want to calculate how much db size in mb a user is used for perticular user out of total database size..

Plz can anybody help me..
I am using php and mysql.

[Scally_Ally]

you could use "table status" to find the size of the total table and of particular rows.

Maybe something like this.
$total = 0;
$sql = "SHOW TABLE STATUS";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
echo "row ".$row['Data_length']+$row['Index_length']."<br>";
$total += $row['Data_length']+$row['Index_length'];
}
echo($total);

I think this is pretty close to the mark but i havent tested it. (as ever)

Ally

[coopster]

Related thread:
MySQL Database Size [webmasterworld.com]

[ash11]

Thanks for your replay..
yes but i want to calculate the size of perticular recods not of total table..is it possible?

[coopster]

To the best of my knowledge there is no "row-level status tool" in MySQL.

I suppose you could select all columns for the particular row and count up the total characters in each column for that row. It would certainly be an ugly kludge though.

Another alternative may be to use a variable percentage. Get the overall table size and use the number of rows you are interested in to calculate it's percentage of the overall table size.

[the_nerd]

It would certainly be an ugly kludge though.

Did you try to "SELECT * FROM table" and then loop through the result: (not sure if strlen "behaves" when it gets date or boolean values - but you can surely refine this. Since you're probably interested in text values only, the rest being of identival size, cou could as well just add up those text fields.)

while ($row = mysql_fetch_assoc ($queryresult)) {
$rowlen = 0;
foreach ($row AS $value) {
$rowlen += strlen ($value);
}
echo $rowlen . "<br>";
}

btw. does anybody know how to indent (tab) program examples here?