quicker/easier/more secure to put pictures in BLOB fields in mysql or in a directory?

[NotGiven]

I am researching the best place to put pictures. I have heard form both
sides and I'd like to know why one is better than the other.

Many thanks!

[Andy Hassall]

Re: quicker/easier/more secure to put pictures in BLOB fields in mysql or in a directory?

On Wed, 18 Aug 2004 15:08:49 -0400, "NotGiven" <noname@nonegiv en.net> wrote:
[color=blue]
>I am researching the best place to put pictures. I have heard form both
>sides and I'd like to know why one is better than the other.[/color]

What are your requirements?

If you've heard from both sides, what did you think of both sides of the
argument, and what were the arguments you read?

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Average_Joe]

Re: quicker/easier/more secure to put pictures in BLOB fields in mysql or in a directory?

In article <yLNUc.114$nW6. 112@bignews4.be llsouth.net>, NotGiven wrote:[color=blue]
> I am researching the best place to put pictures. I have heard form both
> sides and I'd like to know why one is better than the other.
>
> Many thanks![/color]

Like all things, it depends on the goal.

Put images in database under strange circumstances like:

.. Want a uniform backup process, all data in one spot. A DBMS will
guarantee that data cannot be accessed via any other means. (If your app
is something like a revision control system and you want to ensure the
DBMS is the only access gateway.)

.. Want database features (Ie, if the image is deleted than so is
the row, can't delete the file and mess something else up)

A case for this would be images with certain attributes, if the
row were deleted, but not the file, you might have a file w/out
associated information. Chances are this won't be a big deal, and
could easily be dealt with in a cleanup script if it ever were a
problem.

.. Want to allow image uploads, but don't want to leave write perms
on directories, if writing to server filesystem is major no-no, for
instance maybe you want it all run from a CDROM.

.. Space is for some reason cheaper on database (IE: ISP doesn't charge
for mysql space)

.. The unlikely event that you want to search *within* the blob data,
and your DBMS supports this.

.. Need network access to the image, network filesystems won't work for your
case and mirroring is not an option.

.. You have space/database on very strange filesystem such as a mainframe,
the server cannot access the large amount of space available on
the mainframe via filesystem calls. To utilize the space, SQL might be
used.

.. Your boss or client thinks it's best arguing about why filesystems are
more practical fall on deaf ears. ``Hey I paid for this fancy-shmancy
database I'd like to see it used to it's "fullest potential"''

.. When co-operating with existing software is a requirement.

Example:

Your application is a web based version of a desktop networked
application. (Such as a member list with photos) the desktop
application (Client program) fetches all it's data over the wire
from/to the database, and cannot access URL's and such the way a web
browser does.

.. Your particular filesystem is a very poor implementation, prone to
fragmentation and the images change a lot.

.. You want to burn as much CPU and network resources as possible :-)

.. You sell database software and need to show it off.

===============

Reasons against it (and probably many many more):

.. Web server can retrieve image directly. This is a lot faster
for serving static content. With a DBMS, not only do you have to
write special code to send images out, you have to contact the
database for them, giving you more network traffic and making the
database work harder.

.. To use the same BLOB in 2 records, You'd have to create 2 tables, one
for attributes and one for blobs, then cross reference them with a
relation. If you stored just the filename, the same image can be used
with multiple records and only one table. This is handy for a "no
photo available" default image.

.. It's easier to access with system calls.

.. More visable, can be updated easier with conventional tools like ftp
etc..

.. It's a heck of a lot faster and much less wasteful of resources.

.. Don't have to worry about strange differences between DBMS
implementations . (Some databases will alter the data adding \n's
or in other ways distort binary data, making things difficult later
on.

.. It's generally easier to access a file in chunks, a BLOB may or
may not insist on giving it all to you in one fetch.

.. Other tools can process images directly instead of intermediate
extract-to-tmp-file -> process -> insert to database. (Using temp
files to alter working copies is always a good idea, but the same
can be done much easier and more effectively with filesystem calls.)

.. Other people who work on code later on won't scratch their heads
trying to figure out why.

.. It's generally a more standard practice.

.. Filesystems can often locate filenames a lot faster than a search in a
database. (You can still use the database to search other attributes
by using a filename, obviously)

.. More scalable, can move images over to web servers stripped down to
serve static content, freeing the PHP-capable web servers for other
things, database for other "database things".

.. You really don't need to store it in a DBMS, the only time to store an
image in DBMS land is when there is a genuine need for it.


The definitive answer is.... neither. The web has too many grahics as it
is. Text-only web pages are better, so you should just forget the whole
thing. :-)

TIP: Give preference to the filesystem version. With lots and lots
of images, spread them across several directories, the fileystem will
be able to locate the filenames much quicker that way. To select a
directory to store the image, hashing functions generally work good.

Storing hundreds to millions of files in 1 directory can make working
with them an incredible hassle. (Especially when it's time to increase
storage space)

Hope I covered them all. :-)

Jamie
--
http://www.geniegate.com Custom web programming
User Management Solutions Perl / PHP / Java / UNIX

[Zurab Davitiani]

Re: quicker/easier/more secure to put pictures in BLOB fields in mysql or in a directory?

Average_Joe wrote:
[color=blue]
> Storing hundreds to millions of files in 1 directory can make working
> with them an incredible hassle. (Especially when it's time to increase
> storage space)[/color]

Not if you use ReiserFS.