Should I put pictures into a database?

I have an application, where I use pictures. The size of the picture is
about 90kB and to speed up the preview, I made a thumbnail of each
picture which is about 2.5 to 5kB.
I use now a directory structure of ../$a/$b/$c/<pictures>

I wonder if it would be good to put the thumbnails into the current
table, in a different table or leave it like it is now. Same for the
pictures.

What is your opinion and why?

bye

Ronald

Well, this was just hashed out last week, again.

Its a personal thing. I am against it, having seen the slowdown and database bloat caused when this is done. I prefer a pointer to the image to be stored in the table and use that. I have found that after about 12Gb of binarydata gets into the table, it really starts to affect perfomance (in mysql)..

The arguments for storing the image:
- automatically backed up with the db (you do back up, right?)
- stored with the relevant backing data

Against:
- more complex to show image
- db bloat (size of db balloons, may affect cost of storage)
- performance slowdowns as image data grows

regards,

bastien> Date: Wed, 21 Nov 2007 10:14:43 +0800> From: ronald@elmit.com> To:php-general@lists.php.net> Subject: [php] Should I put pictures into a database?> > I have an application, where I use pictures. The size of the picture is> about 90kB and to speed up the preview, I made a thumbnail of each>picture which is about 2.5 to 5kB.> I use now a directory structure of ../$a/$b/$c/<pictures>> > I wonder if it would be good to put the thumbnails into the current> table, in a different table or leave it like it is now. Same for the> pictures.> > What is your opinion and why?> > bye> > Ronald> > -- > PHP General Mailing List (http://www.php.net/)> To unsubscribe, visit:http://www.php.net/unsub.php>
__________________________________________________ _______________
Send a smile, make someone laugh, have some fun! Start now!
http://www.freemessengeremoticons.ca/?icid=EMENCA122

On Wednesday 21 November 2007 03:14:43 Ronald Wiplinger wrote:
> I have an application, where I use pictures. The size of the picture is
> about 90kB and to speed up the preview, I made a thumbnail of each
> picture which is about 2.5 to 5kB.
> I use now a directory structure of ../$a/$b/$c/<pictures>
>
> I wonder if it would be good to put the thumbnails into the current
> table, in a different table or leave it like it is now. Same for the
> pictures.
>
> What is your opinion and why?

Ouch this particular piece of topic is a all time champion of things to
discuss...
Richard Lynch is pretty good at beating that old horse ;D (you there?)

I would say it's up to you and the project...
I've made a couple of pedigrees and membership lists where I've chosen to put
images in the database for the reason of simplicity.
10.000 small images really gets me depressed when viewed as files.

_I_ feel that a list of ... lets say 250 employees is far more compact and
corrrect in a blob; small program and everything piled in one place.
The previous mailer talked about huge blob data, mine will never come close
12gb of blob data.

I've made a couple of forums where multiple image uploading and expanding
possibilities made it awkward to use blobs.

As I said, if youre comfortable with using files in a blob rather than remote
filesystem, and the project works OK with it, why not.
On the other hand, none've my tables passed 1gb yet.
>
> bye
>
> Ronald



--
---
Børge Holen
http://www.arivene.net

Op 21-nov-07, om 03:45 heeft Bastien Koert het volgende geschreven:

>
> Well, this was just hashed out last week, again.
>
> Its a personal thing. I am against it, having seen the slowdown and
> database bloat caused when this is done. I prefer a pointer to the
> image to be stored in the table and use that. I have found that
> after about 12Gb of binary data gets into the table, it really
> starts to affect perfomance (in mysql).

Using a pointer just makes more sense to me.
>
> The arguments for storing the image:
> - automatically backed up with the db (you do back up, right?)
> - stored with the relevant backing data
>
> Against:
> - more complex to show image
> - db bloat (size of db balloons, may affect cost of storage)
> - performance slowdowns as image data grows
>
> regards,
>
> bastien> Date: Wed, 21 Nov 2007 10:14:43 +0800> From:
> ronald@elmit.com> To: php-general@lists.php.net> Subject: [php]
> Should I put pictures into a database?> > I have an application,
> where I use pictures. The size of the picture is> about 90kB and to
> speed up the preview, I made a thumbnail of each> picture which is
> about 2.5 to 5kB.> I use now a directory structure of ../$a/$b/$c/
> <pictures>> > I wonder if it would be good to put the thumbnails
> into the current> table, in a different table or leave it like it
> is now. Same for the> pictures.> > What is your opinion and why?> >
> bye> > Ronald> > -- > PHP General Mailing List (http://www.php.net/)
> > To unsubscribe, visit: http://www.php.net/unsub.php>
> __________________________________________________ _______________
> Send a smile, make someone laugh, have some fun! Start now!
> http://www.freemessengeremoticons.ca/?icid=EMENCA122

if you have a lot of images 5000 and more
use a structured storingmethod in the filesystem
like 10 folders numbered from 0-9 and so on, because
folders with an extrem amount of files are really slow
in some filesystems.. if you have lesser than 5000 images
you could store the images in the database.. it´s extremly
usefull if you want to access the images from more than one
source. so you don´t have to worry about how to implement http
or ftp to download the images from a fileserver in another project
because you already have the database connection.

Jonas Geiregat schrieb:
>
> Op 21-nov-07, om 03:45 heeft Bastien Koert het volgende geschreven:
>
>>
>> Well, this was just hashed out last week, again.
>>
>> Its a personal thing. I am against it, having seen the slowdown and
>> database bloat caused when this is done. I prefer a pointer to the
>> image to be stored in the table and use that. I have found that after
>> about 12Gb of binary data gets into the table, it really starts to
>> affect perfomance (in mysql).
>
> Using a pointer just makes more sense to me.
>>
>> The arguments for storing the image:
>> - automatically backed up with the db (you do back up, right?)
>> - stored with the relevant backing data
>>
>> Against:
>> - more complex to show image
>> - db bloat (size of db balloons, may affect cost of storage)
>> - performance slowdowns as image data grows
>>
>> regards,
>>
>> bastien> Date: Wed, 21 Nov 2007 10:14:43 +0800> From:
>> ronald@elmit.com> To: php-general@lists.php.net> Subject: [php] Should
>> I put pictures into a database?> > I have an application, where I use
>> pictures. The size of the picture is> about 90kB and to speed up the
>> preview, I made a thumbnail of each> picture which is about 2.5 to
>> 5kB.> I use now a directory structure of ../$a/$b/$c/<pictures>> > I
>> wonder if it would be good to put the thumbnails into the current>
>> table, in a different table or leave it like it is now. Same for the>
>> pictures.> > What is your opinion and why?> > bye> > Ronald> > -- >
>> PHP General Mailing List (http://www.php.net/)> To unsubscribe, visit:
>> http://www.php.net/unsub.php>
>> __________________________________________________ _______________
>> Send a smile, make someone laugh, have some fun! Start now!
>> http://www.freemessengeremoticons.ca/?icid=EMENCA122

At 10:14 AM +0800 11/21/07, Ronald Wiplinger wrote:
>I have an application, where I use pictures. The size of the picture is
>about 90kB and to speed up the preview, I made a thumbnail of each
>picture which is about 2.5 to 5kB.
>I use now a directory structure of ../$a/$b/$c/<pictures>
>
>I wonder if it would be good to put the thumbnails into the current
>table, in a different table or leave it like it is now. Same for the
>pictures.
>
>What is your opinion and why?

I use both depending on need.

One fact that is seldom addressed is that computational speed is
increasing while storage costs are decreasing. As such, I suspect in
a very short while, it won't make much difference regardless.

Even now, while there is much debate over what to do, it isn't until
you reach the maximums that degrades in service becomes significant
when comparing the two methods.

I don't know specifically how MySQL stores BLOB data, but databases
typically use pointers to files (binary-tree nodes) and not the files
themselves. So, the arguments of: pictures shouldn't be stored in a
dB because there is nothing there to search; and I would rather use a
pointer instead; are kind of moot.

After all is said and done, no matter what you do with a picture
(file system or dB) it is stored on a hard drive. So in a sense, a dB
is not much more than a file system with more options.

I am sure sometime in the future. programmers will look back on our
debates and ask "What hell was all that about? Didn't these guys
realize that a filing system is just another database?"

Cheers,

tedd
--
-------
http://sperling.com http://ancientstones.com http://earthstones.com

I use a custom file system that caches large amounts of data in RAM.
That way it's really fast and as easy to use as normal file system calls.

--
Michael McGlothlin
Southwest Plumbing Supply

Michael McGlothlin wrote:
> I use a custom file system that caches large amounts of data in RAM.
> That way it's really fast and as easy to use as normal file system calls.

It's called a relational database ;)
Seriously.
Databases like MySQL do not handle BLOB's with any sensible control hence the
againsts quoted. A decent database working in cooperation with the operating
system should be as fast at returning a block of raw data as searching the
file system to find the same block and hand that data to the following process
transparently.
Firebird has always handled BLOB's reasonably well, and with the incremental
backup facilities now available, ALL of the data can be safely managed and
replicated to secondary machines. Managing 100s of thousands of images in a
directory structure may not be the best way of managing a large volume of
data, requiring searching through a tree of directories to get to a file,
while storing the same in a flat file system WITHIN a database SHOULD be a
more efficient alternative.

Should I put pictures into a database? - depends on both the data base and the
file system/OS :)

--
Lester Caine - G8HFL
-----------------------------
Contact - http://home.lsces.co.uk/lsces/wiki/?page=contact
L.S.Caine Electronic Services - http://home.lsces.co.uk
MEDW - http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Firebird - http://www.firebirdsql.org/index.php

Michael McGlothlin wrote:
> I use a custom file system that caches large amounts of data in RAM.
> That way it's really fast and as easy to use as normal file system calls.

It's called a relational database ;)
Seriously.
Databases like MySQL do not handle BLOB's with any sensible control hence the
againsts quoted. A decent database working in cooperation with the operating
system should be as fast at returning a block of raw data as searching the
file system to find the same block and hand that data to the following process
transparently.
Firebird has always handled BLOB's reasonably well, and with the incremental
backup facilities now available, ALL of the data can be safely managed and
replicated to secondary machines. Managing 100s of thousands of images in a
directory structure may not be the best way of managing a large volume of
data, requiring searching through a tree of directories to get to a file,
while storing the same in a flat file system WITHIN a database SHOULD be a
more efficient alternative.

Should I put pictures into a database? - depends on both the data base and the
file system/OS :)

--
Lester Caine - G8HFL
-----------------------------
Contact - http://home.lsces.co.uk/lsces/wiki/?page=contact
L.S.Caine Electronic Services - http://home.lsces.co.uk
MEDW - http://home.lsces.co.uk/ModelEngineersDigitalWorkshop/
Firebird - http://www.firebirdsql.org/index.php

On Thu, 2007-11-22 at 06:22 +0000, Lester Caine wrote:
> Michael McGlothlin wrote:
> > I use a custom file system that caches large amounts of data in RAM.
> > That way it's really fast and as easy to use as normal file system calls.
>
> It's called a relational database ;)
> Seriously.
> Databases like MySQL do not handle BLOB's with any sensible control hence the
> againsts quoted. A decent database working in cooperation with the operating
> system should be as fast at returning a block of raw data as searching the
> file system to find the same block and hand that data to the following process
> transparently.
> Firebird has always handled BLOB's reasonably well, and with the incremental
> backup facilities now available, ALL of the data can be safely managed and
> replicated to secondary machines. Managing 100s of thousands of images in a
> directory structure may not be the best way of managing a large volume of
> data, requiring searching through a tree of directories to get to a file,
> while storing the same in a flat file system WITHIN a database SHOULD be a
> more efficient alternative.
>
> Should I put pictures into a database? - depends on both the data base and the
> file system/OS :)

There's lots of reasons to do either. For one project I keep images in a
database. When the browser makes a request for the image via a specially
crafted URL it either gets a direct hit or invokes PHP's 404 handler.
Using the 404 handler I can then pull the image out of the database,
scale it's dimensions according to the URL and save to filesystem and
then set the header to status 200 and flush the image. using this
technique subsequent requests come from the file system while internally
I have the convenience of the database. Once a day I have a cron job
traverse the image version directory and purge any older than X days.

Cheers,
Rob.
--
.................................................. ..........
SwarmBuy.com - http://www.swarmbuy.com

Leveraging the buying power of the masses!
.................................................. ..........