469,889 Members | 1,365 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,889 developers. It's quick & easy.

blob size

Hi,

I have a question about the size of a blob if it indeed needs to be set
and cannot be variable. I ask this question because I am trying to save
web pages to a database but a web page can be anywhere from 10
characters to 200,000 characters and using blob(200000) is very
inefficient.
I was thinking of using various size tables ready for various size
pages but I would be easier to manage having them all in one table.

Anyone have any input?

Thanks

Carl

Nov 27 '06 #1
4 68391
<ca**********@gmail.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
Hi,

I have a question about the size of a blob if it indeed needs to be set
and cannot be variable. I ask this question because I am trying to save
web pages to a database but a web page can be anywhere from 10
characters to 200,000 characters and using blob(200000) is very
inefficient.
I was thinking of using various size tables ready for various size
pages but I would be easier to manage having them all in one table.

Anyone have any input?

Thanks

Carl
Carl,
I think you're confused. BLOBs are not 'zero filled' and only take up
the space you put in them plus a couple bytes, like this:

L = length of data, # = overhead

BLOB = L + 2 bytes (max size is 2^16 - 1 or 65,535 bytes, 65KB)
MEDIUMBLOB = L + 3 bytes (max size is 2^24 - 1 or 16,777,215 bytes, 16MB)
LONGBLOB = L + 4 bytes (max size is 2^32 - 1 or 4,294,967,295 bytes, 4GB)

....you obviously need to use a MEDIUMBLOB and no, a 1KB web page will not
consume 16MB of storage space in the database. Using different tables for
different sized documents would be a nightmare at best anyway.

Norm
--
FREE Avatar hosting at www.easyavatar.com

Nov 29 '06 #2
Thanks for the reply Norm,

I think I understood what you are explaining but just to confirm that I
have if I use the following sql statement;

create table page(page mediumblob(16777215));

should work for what I am doing. This way the inserted pages won't be
truncated and minimum disk space will be used.

Thanks again,
Carl
Norman Peelman wrote:
<ca**********@gmail.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
Hi,

I have a question about the size of a blob if it indeed needs to be set
and cannot be variable. I ask this question because I am trying to save
web pages to a database but a web page can be anywhere from 10
characters to 200,000 characters and using blob(200000) is very
inefficient.
I was thinking of using various size tables ready for various size
pages but I would be easier to manage having them all in one table.

Anyone have any input?

Thanks

Carl

Carl,
I think you're confused. BLOBs are not 'zero filled' and only take up
the space you put in them plus a couple bytes, like this:

L = length of data, # = overhead

BLOB = L + 2 bytes (max size is 2^16 - 1 or 65,535 bytes, 65KB)
MEDIUMBLOB = L + 3 bytes (max size is 2^24 - 1 or 16,777,215 bytes, 16MB)
LONGBLOB = L + 4 bytes (max size is 2^32 - 1 or 4,294,967,295 bytes, 4GB)

...you obviously need to use a MEDIUMBLOB and no, a 1KB web page will not
consume 16MB of storage space in the database. Using different tables for
different sized documents would be a nightmare at best anyway.

Norm
--
FREE Avatar hosting at www.easyavatar.com
Nov 30 '06 #3
Norman Peelman wrote:
<ca**********@gmail.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
Hi,
>
I have a question about the size of a blob if it indeed needs to be
set
and cannot be variable. I ask this question because I am trying to
save
web pages to a database but a web page can be anywhere from 10
characters to 200,000 characters and using blob(200000) is very
inefficient.
I was thinking of using various size tables ready for various size
pages but I would be easier to manage having them all in one table.
>
Anyone have any input?
>
Thanks
>
Carl
>
Carl,
I think you're confused. BLOBs are not 'zero filled' and only take
up
the space you put in them plus a couple bytes, like this:

L = length of data, # = overhead

BLOB = L + 2 bytes (max size is 2^16 - 1 or 65,535 bytes, 65KB)
MEDIUMBLOB = L + 3 bytes (max size is 2^24 - 1 or 16,777,215 bytes,
16MB)
LONGBLOB = L + 4 bytes (max size is 2^32 - 1 or 4,294,967,295 bytes,
4GB)

...you obviously need to use a MEDIUMBLOB and no, a 1KB web page will
not
consume 16MB of storage space in the database. Using different tables
for
different sized documents would be a nightmare at best anyway.
>>
<ca**********@gmail.comwrote in message
news:11********************@80g2000cwy.googlegroup s.com...
Thanks for the reply Norm,

I think I understood what you are explaining but just to confirm that I
have if I use the following sql statement;

create table page(page mediumblob(16777215));

should work for what I am doing. This way the inserted pages won't be
truncated and minimum disk space will be used.

Thanks again,
Carl

You don't specify the size at all (in fact, you can't, at least in MySQL4.1)
so:

create table page (page MEDIUMBLOB)

.... is what your after.

Norm
Nov 30 '06 #4
Thanks again Norm!

Norman Peelman wrote:
Norman Peelman wrote:
<ca**********@gmail.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...
Hi,

I have a question about the size of a blob if it indeed needs to be
set
and cannot be variable. I ask this question because I am trying to
save
web pages to a database but a web page can be anywhere from 10
characters to 200,000 characters and using blob(200000) is very
inefficient.
I was thinking of using various size tables ready for various size
pages but I would be easier to manage having them all in one table.

Anyone have any input?

Thanks

Carl

>
Carl,
I think you're confused. BLOBs are not 'zero filled' and only take
up
the space you put in them plus a couple bytes, like this:
>
L = length of data, # = overhead
>
BLOB = L + 2 bytes (max size is 2^16 - 1 or 65,535 bytes, 65KB)
MEDIUMBLOB = L + 3 bytes (max size is 2^24 - 1 or 16,777,215 bytes,
16MB)
LONGBLOB = L + 4 bytes (max size is 2^32 - 1 or 4,294,967,295 bytes,
4GB)
>
...you obviously need to use a MEDIUMBLOB and no, a 1KB web page will
not
consume 16MB of storage space in the database. Using different tables
for
different sized documents would be a nightmare at best anyway.
>
>
<ca**********@gmail.comwrote in message
news:11********************@80g2000cwy.googlegroup s.com...
Thanks for the reply Norm,

I think I understood what you are explaining but just to confirm that I
have if I use the following sql statement;

create table page(page mediumblob(16777215));

should work for what I am doing. This way the inserted pages won't be
truncated and minimum disk space will be used.

Thanks again,
Carl


You don't specify the size at all (in fact, you can't, at least in MySQL4.1)
so:

create table page (page MEDIUMBLOB)

... is what your after.

Norm
Nov 30 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by hamvil79 | last post: by
2 posts views Thread by Stanley Sinclair | last post: by
1 post views Thread by Markusek Peter | last post: by
2 posts views Thread by pmz | last post: by
2 posts views Thread by pike | last post: by
3 posts views Thread by deepaa | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.