blob size

[carlbernardi]

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

[Norman Peelman]

<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

[carlbernardi]

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

[Norman Peelman]

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

[carlbernardi]

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