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

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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!

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

Quoted text here. Click to load it

 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?

<http://www.andyhsoftware.co.uk/space Space: disk usage analysis tool

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

Quoted text here. Click to load it

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

.. 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

.. 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.

  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

.. 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

.. 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. :-)

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

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

Average_Joe wrote:

Quoted text here. Click to load it

Not if you use ReiserFS.

Site Timeline