Page 1 of 1
Calculating Space In DB
Posted: Fri Dec 22, 2006 2:03 am
by aliasxneo
Hello all,
I need to find a method for calculating the space that a certain amount of data is taking in the database. What I'm really trying to do is limit the amount of emails someone may have so I need to grab all their emails and use some kind of algorithm to determine how many megabytes the data is taking up.
Is this possible? Thanks.
Cheers,
- Josh
Posted: Fri Dec 22, 2006 2:20 am
by Rovas
You can limit the rows of data in the table using AVG_ROW_LENGTH and MAX_ROWS options from CREATE TABLE or ALTER TABLE. You can create table as large as 4 Gb. Also you can compress using myisampack or use the archive or csv engines (the last two don't support indexing). For more information download the MySQL manual.
UPDATE: There is a php function that display the amount of free space in a directory:
Posted: Fri Dec 22, 2006 3:06 am
by aliasxneo
Rovas wrote:You can limit the rows of data in the table using AVG_ROW_LENGTH and MAX_ROWS options from CREATE TABLE or ALTER TABLE. You can create table as large as 4 Gb. Also you can compress using myisampack or use the archive or csv engines (the last two don't support indexing). For more information download the MySQL manual.
UPDATE: There is a php function that display the amount of free space in a directory:
Thanks but that doesn't really help me much. Limiting the rows in the table would impair all the users, rather than each invidual one, which is what I am aiming for. My plan is to run a check when another email is inserted on how much data is already taken by that user so that I can decide whether or not to insert the email and notify the user or not.
Oh, and I didn't see anything on using disk_free_space with MySQL.
Posted: Fri Dec 22, 2006 3:19 am
by timvw
Untested:
Code: Select all
SELECT SUM(BIT_LENGTH(blobfieldname)) FROM data WHERE user_id=1
Posted: Fri Dec 22, 2006 3:24 am
by aliasxneo
timvw wrote:Untested:
Code: Select all
SELECT SUM(BIT_LENGTH(blobfieldname)) FROM data WHERE user_id=1
Well, it worked. It returned 4278, what is that in? Bytes, Kb's? Thanks for the code

Posted: Fri Dec 22, 2006 3:39 am
by malcolmboston
anyway of getting the whole database size?
Posted: Fri Dec 22, 2006 3:54 am
by dibyendrah
It's byte not kilobyte I guess.
Posted: Fri Dec 22, 2006 4:33 am
by timvw
No need to guess... Here's a pretty definitive answer:
http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html.
(Btw, you might want to have a look at the LENGTH function too...)
Posted: Fri Dec 22, 2006 2:29 pm
by aliasxneo
Is there a way to grab the bit size of a string in PHP? I would like to check the size of an email before inserting it into the database so that I know it's not too big.