Calculating Space In DB

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
aliasxneo
Forum Contributor
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Calculating Space In DB

Post 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
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post 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:

Code: Select all

disk_free_space()
aliasxneo
Forum Contributor
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Post 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:

Code: Select all

disk_free_space()
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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Untested:

Code: Select all

SELECT SUM(BIT_LENGTH(blobfieldname)) FROM data WHERE user_id=1
aliasxneo
Forum Contributor
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Post 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 :)
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

anyway of getting the whole database size?
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post by dibyendrah »

It's byte not kilobyte I guess.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...)
aliasxneo
Forum Contributor
Posts: 136
Joined: Thu Aug 31, 2006 12:01 am

Post 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.
Post Reply