8

Possible Duplicate:
Storing Images in DB - Yea or Nay?
Images in database vs file system

I've been developing a web application using RIA technologies (Flex + PHP + MySQL + Ajax) and now I'm in a dilemma about image files.

I use some images in my Flex app, so I think "it could be awesome if I store them into database, and then retrieve from it; consecuently, maintain process should be more easy". But, here is my dilemma:

Should I store the physical URL of my images, or is going to be better if I store directly the image?

For example, should my Cars table looks like:

ID (autonumeric) | Source (text)

or like this?

ID (autonumeric) | Image (longblob or blob)

I know that here are cool people that can answer me this question, explaining me which is better and why :)

Community
  • 1
  • 1
Fran Verona
  • 5,438
  • 6
  • 46
  • 85

6 Answers6

6

I personally recommend to Store Images in the database. Of course it both advantages and disadvantages.

Advantages of storing BLOB data in the database:

  • It is easier to keep the BLOB data synchronized with the remaining items in the row.
  • BLOB data is backed up with the database. Having a single storage system can ease administration.
  • BLOB data can be accessed through XML support in MySQL, which can return a base 64–encoded representation of the data in the XML stream.
  • MySQL Full Text Search (FTS) operations can be performed against columns that contain fixed or variable-length character (including Unicode) data. You can also perform FTS operations against formatted text-based data contained within image fields—for example, Microsoft Word or Microsoft Excel documents.

Disadvantages of Storing BLOB Data in the Database:

Carefully consider what resources might be better stored on the file system rather than in a database. Good examples are images that are typically referenced via HTTP HREF. This is because:

  • Retrieving an image from a database incurs significant overhead compared to using the file system.
  • Disk storage on database SANs is typically more expensive than storage on disks used in Web server farms.
Farinha
  • 17,636
  • 21
  • 64
  • 80
ScoRpion
  • 11,364
  • 24
  • 66
  • 89
  • I am not an expert on DBs at all, but what about result caching and DB server RAM usage? Wouldn't it increase a lot since the blobs would get all loaded into ram? – Vinicius Kamakura Nov 18 '11 at 12:34
4

As a general rule you wan't to keep your databases small, so they perform better (and backup better too). So if you can store only a filesystem reference (path + filename) or URL in the DB, that would be better.

Vinicius Kamakura
  • 7,665
  • 1
  • 29
  • 43
  • Disagree. Large databases can perform just as well as small ones if correctly administered. Databases are also an ideal platform for the storage of images; Accessing these directly from the DB can solve a lot of issues when using larger systems, such as multiple web servers where you would have to start using shared storage (has it's own issues) or replicate files (more issues). – ChrisBint Nov 18 '11 at 12:25
  • 1
    @ChrisBint If you have a web gallery for example, and store the images on the DB, all the work and traffic would have to go through the DB server. If you store paths/urls, you can share the load with the multiple web servers you are talking about. But each situation requires different approaches, hence I said it is a general rule not a set-in-stone law. – Vinicius Kamakura Nov 18 '11 at 12:30
  • You can't "share the load" with multiple servers because you need to figure out how to share the files. NFS? No way ... if you lost the NFS share, then what? At least with a DB, you're syncing them and can resort to a master-slave if needed. – sdot257 Nov 18 '11 at 13:57
  • @luckytaxi yes you can. there are several options to synchronize files in directories, rsync is a common example. Just because you don't know how to do it, don't assume everyone can't too. – Vinicius Kamakura Nov 18 '11 at 16:31
  • @hexa haha, seriously? rsync is good and all and I use it religiously but there are BETTER options out there if you want to go that route. How will you push/pull the data? When will you perform this? rsync is only one way, so what happens if B is different than A if A is the authorize server? I would look into Unison or something like DRDB, you know something that's more reliable and meant for something in a clustered environment. – sdot257 Nov 19 '11 at 12:43
4

Its probably a question of personal preference.

As a general rule its better to keep the database small. However when you come to enterprise applications they regulary add the images directly to the database. If you place them on the file system the db and your file system can get out of sync.

Larger CMS will regulary place those files in the db. However be aware that this requires a larger DB sizing when everything is growing...

When you are saving the url and name only, be sure that these won't change in the future.

With files stored in the database you can implement security easier and you don't have to worry about duplicate filenames.

Udo Held
  • 12,314
  • 11
  • 67
  • 93
1

I used to store the path into the URL, but then adding an additional web server to the mix proved less than ideal. For one thing, you'll have to share the path to where the images are stored. We were using NFS and it became slow after a while. We tried syncing the files from one web server to another but the process became cumbersome.

Having said that, I would store them in the DB. I've since moved all my image/file storage over to MongoDB. I know this doesn't satisfy your needs but we've tried it all (even S3) and we weren't happy with the other solutions. If we had to, I would definite throw them inside MySQL.

sdot257
  • 10,046
  • 26
  • 88
  • 122
1

Personally, I've always stored the URL.

There's no real reason not to store the image directly in the database, but there are benefits to not storing it in the database.

You get more flexibility when you don't store the image in the database. You can easily move it around and just update the URL in the file. So, if you wanted to move the image from your webserver to a service such as Flickr or Amazon Web Services, it would just be as easy as updating the link to the new files. That also gives you easy access to content delivery networks so that the images are delivered to end users quicker.

Brian Hoover
  • 7,861
  • 2
  • 28
  • 41
0

I'd store the url, it's less data and that means a smaller database and faster data fetching from it ;)

Kevin
  • 2,739
  • 33
  • 57