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

Re: blob size

wrote in message
news:1164597501.583159.109350@j44g2000cwa.googlegroups.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

Re: blob size

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:
> wrote in message
> news:1164597501.583159.109350@j44g2000cwa.googlegroups.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

Re: blob size

> Norman Peelman wrote:
> > wrote in message
> > news:1164597501.583159.109350@j44g2000cwa.googlegroups.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.
> >

>>
wrote in message
news:1164856265.679069.4230@80g2000cwy.googlegroups.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

Re: blob size

Thanks again Norm!



Norman Peelman wrote:
> > Norman Peelman wrote:
> > > wrote in message
> > > news:1164597501.583159.109350@j44g2000cwa.googlegroups.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.
> > >
>
> >>
> wrote in message
> news:1164856265.679069.4230@80g2000cwy.googlegroups.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