SitePoint Sponsor

User Tag List

View Poll Results: Do you store images in your sql database?

Voters
71. You may not vote on this poll
  • Yes, always

    1 1.41%
  • Sometimes

    11 15.49%
  • No, never

    48 67.61%
  • I would, but I don't know how

    11 15.49%
  • what ist a mysql database?

    0 0%
Page 1 of 4 1234 LastLast
Results 1 to 25 of 87
  1. #1
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Berlin
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Why not save images in SQL Database?

    Hi,

    I've read a couple of threads here where people tried to find out how to store image files in a mysql database.
    The answer was always: Don't do it.

    Now I have two questions:

    1) Why not? As far as I know even this board (vBulletin) stores attachments an avatars in the mysql database - so why shouldn't i do it???
    Is it not even smarter sometimes? Like for backup reasons. Imagen you hav vB running and images ar always stored in some folders. For a backup you don't only have to backup the databes, but also those folders...

    2)Now if I really want to do it - no matter if wise or not. What do I have to make different to queries?

    Fl÷zen

  2. #2
    SitePoint Wizard Dangermouse's Avatar
    Join Date
    Oct 2003
    Posts
    1,024
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're reading/writing data to the db it may get corrupt, you may miss say one byte off and screw it all up. Its not very portable, its probably slower seems you have to query the DB, will add extra queries to the page, more code just to display an image..

  3. #3
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    BLOB data can hinder performance of the database if done on a massive scale. Since database space/time/etc. are usually more expensive than disk space, it's usually more efficient to store a reference to the image's path on the filesystem in the database rather than the actual image.

  4. #4
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Berlin
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So why vB doesn't do it?

  5. #5
    With More ! for your $ maxor's Avatar
    Join Date
    Feb 2004
    Location
    Scottsdale, Arizona
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you sure vB stores the actual image and not just a path to the image in the db?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    why not?

    one really good reason: an image pulled from the database has a URL that is not going to allow the browser to cache the image

    browser image caching is very important if you want to have a fast-loading site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From the fact that some web hosts like the one I use explicitly prohibit the storing of images or blobs within databases, I imagine doing such much have a tremendous hit in performance.

    I also agree that the browser being able to cache an image is important if one wants to improve how quickly a site loads.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  8. #8
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you sure vB stores the actual image and not just a path to the image in the db?
    Yep

    Look at everyone's avatar image locations, for example.

  9. #9
    Phil fillup07's Avatar
    Join Date
    May 2002
    Location
    Jacksonville, FL
    Posts
    1,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    an image pulled from the database has a URL that is not going to allow the browser to cache the image
    are you sure about this? why can't a browswer cache http://www.sitepoint.com/forums/imag...753&dateline=0 as an image?

  10. #10
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fillup07
    are you sure about this? why can't a browswer cache http://www.sitepoint.com/forums/imag...753&dateline=0 as an image?
    I beleive it's because the date of image creation will appear different everytime?

    As for vBulletin storing images this way, I can only assume it's to prevent hassles with downloading/uploading full backups, not having to worry about different hosting setups and permissions. Bad idea IMO though! first thing I do with any of my forums is swap to the 'filesystem' setup (vB3 has that built in as an option now!)

  11. #11
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Berlin
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by KLB
    From the fact that some web hosts like the one I use explicitly prohibit the storing of images or blobs within databases, I imagine doing such much have a tremendous hit in performance.
    Sure, if you are having some hundred people on your webserver, all using blobs in their database and call the everytime, this might take performance.
    But since root-servers became quit cheep, that should not be an argument for someone having his own 2GHz Server...

    Quote Originally Posted by KLB
    I also agree that the browser being able to cache an image is important if one wants to improve how quickly a site loads.
    Well, first I guess you can use mod_rewrite for images too. After that these images should appear for the browser like nomal image files.
    Secound, what about things like jpcache on the serverside? You could set the cachhing of images to 1 hour, that should speed up the thing!

    Quote Originally Posted by Dangermouse
    If you're reading/writing data to the db it may get corrupt, you may miss say one byte off and screw it all up.
    So I better not use a database at all?
    Why is the risk screwing up the database when write blobs higher than with text??

    Fl÷zen
    Last edited by Floezen; Apr 15, 2004 at 03:20.

  12. #12
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are good reasons to keep images INSIDE the database :
    - backups are easy (everything fits into one file, backup and restore is a single line operation)
    - if you have a cluster of several web servers, the database then act as a central repository for images so that each web server can get the images, without having synchronisation/distribution/concurency problems (doing the same thing with plain files is a lot more tricky)
    - coherency : if you use an image file and a file path in the database, you could have the file go away and the entry still there in the database (your data structure would then be corrupted). You don't have that problem if you keep the image in the database itself (and you can use transactions to make sure everything is always right)

    As far as speed goes :
    - caching on the browser is not an issue, provided you write your code to issue the right HTTP header with correct expiration info
    - the web server can cache locally the pictures issued by the database, this avoid stressing the database and gets you the same speed as files
    - mod_rewrite can make it look like the image is really a file, while it is actually served by a script from the database/server-cache

    The only issue is size (databases don't like GB of blob datas), but if the amount of picture is reasonable, the database will handle them fine.

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Our DBMS had 10GB of text data and handled it just fine. The performance issues are mostly myths. Put the images where ever you please.

  14. #14
    + platinum's Avatar
    Join Date
    Jun 2001
    Location
    Adelaide, Australia
    Posts
    6,441
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    Our DBMS had 10GB of text data and handled it just fine. The performance issues are mostly myths. Put the images where ever you please.
    While I'd hate to disagree with the database jedi I know of a few vBulletin based forums who have suffered horribly from attachments being stored in this way, simply moving them to be stored in the filesystem solved it all.

  15. #15
    SitePoint Wizard silver trophy KLB's Avatar
    Join Date
    Nov 2003
    Location
    Maine USA
    Posts
    3,781
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From what MattR and Platinum stated, I suspect that putting blogs inside of a database is a fine practice -- if it is done and used correctly. The key word is "if". I suspect not completely understanding the issue can cause greater performance issues and reliablity issues than simply storing files in the file system. Since most "webmasters" don't fully understand the issues, and large enough population know just enough to be dangerous, web hosts like mine simply ban the practice.
    Ken Barbalace: EnvironmentalChemistry.com (Blog, Careers)
    InternetSAR.org
    Volunteers Assist Search and Rescue via Internet
    My Firefox Theme: Classic Compact
    Based onFirefox's default theme but uses much less window space

  16. #16
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by platinum
    While I'd hate to disagree with the database jedi I know of a few vBulletin based forums who have suffered horribly from attachments being stored in this way, simply moving them to be stored in the filesystem solved it all.
    That is, perhaps, a symptom of a mis-configured server. A filesystem *is* a database (and an OS contains part of a DBMS) and vice versa, so really it's a matter of implementation whether or not your DBMS can pull from a filesystem as efficiently. However, DBMS products usually offer finer-grained access control, page caching, page fetching, and optimzations not found in filesystems, so properly configured you can make it just as fast, if not moreso.

    I should have clarified but that was taken care of: KLB's post is spot-on.

  17. #17
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Put images in a database ? Never heard as much crap in my life.

    The role of a database is to relate dynamic data, not dumb data.

    Cannot agree in any way with the comments posted so far about it being okay to dump images to a database.

    Ahem

  18. #18
    SitePoint Enthusiast
    Join Date
    Feb 2004
    Location
    France
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Widow Maker
    Put images in a database ? Never heard as much crap in my life.

    The role of a database is to relate dynamic data, not dumb data.
    Images are not anymore "dumb" than text, it's just a different form of encoding. Just as one needs ACID to manipulate text, one can need ACID to handle pictures.

    I don't see why database should be confined to handling text forever, the days of COBOL and text-only terminal is over. If anything, I believe database vendors shoull work to make their database smarter when handling non-text datas (there are already data types for geographical coordinates, why not for picture and sound ?)

  19. #19
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Indeed. If your DBMS was smart, then you could use it to perform all sorts of things on images that you'd be hard pressed to do now without some sort of image library.

    For example, your DBMS could include functions to return the height and width of the image, or methods to compare two images, etc.

  20. #20
    Non-Member
    Join Date
    Jan 2004
    Location
    Planet Earth
    Posts
    1,764
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For example, your DBMS could include functions to return the height and width of the image, or methods to compare two images, etc.
    What a load of ******** Why'd you want to implement logic such as this into a database for ? There is absolutely no point.


  21. #21
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because it's not a database -- it's a database management system. You'd need equals (=) in order to implement uniqueness constraints.

    Height and width functions would be used (internally) to allow you to define attributes for the image.

    For example, you'd have something like:
    image(ID, image_data (unique), etc. )
    image_view( ID, height, width, bit_depth, image_data, etc. )

    When you insert into image, the rest would be populated for you in the view.

  22. #22
    SitePoint Evangelist IJoeR's Avatar
    Join Date
    Feb 2003
    Location
    Somewhere in, MD
    Posts
    400
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd say don't store the images in the database. I don't and i probably never will, though it is a cool feature, but maybe not the best.

    From what I can see it will effect speed:
    Compare the time it will take to load an image from the database vs. a path to an image file. Naturally, the path will load much quicker because it is a million times smaller.

    Plus, i don't like to put all my eggs in one basket, so putting images and all my text in a database, if my db goes corrupt and i have no backup (that would be my fault) I lose everything.

    Thats my 2 cents. Maybe run some loading tests. See how fast a query to load an image stored in the DB takes vs. making a query to the db to load the just the path -- ftp your image up in the proper location.

  23. #23
    Non-Member
    Join Date
    Jan 2003
    Posts
    866
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by platinum
    While I'd hate to disagree with the database jedi I know of a few vBulletin based forums who have suffered horribly from attachments being stored in this way, simply moving them to be stored in the filesystem solved it all.
    I'm one of those that have run into BIG problems with VBs image table in the DB. I had to restore my database with a backup and lost days worth of data due to a images table related crash.

    If you store a lot of photos - store them in the file system.

  24. #24
    SitePoint Guru
    Join Date
    Feb 2004
    Location
    Oregon
    Posts
    686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the only thing I have heard is if you keep images in the db it becomes huge fast. some people talk about keeping 100K+ files in it. if vb does it then the files are very small compared to what everybody else wants to do with them. when the size of the db gets big then it leads to perfomance problems. but I think if the image is small 1-10K then I don't see a issue, unless you keep a million of those then it could lead to bigger problems.
    success is not by chance, it is by choice.

  25. #25
    Non-Member
    Join Date
    Jan 2003
    Posts
    866
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    From Berber's blog:

    "If you are dealing with a big number of images (tens of thousands or hundreds of thousands) you need to keep a very specific directory structure with sub directories to avoid file system performance limitations. In this case, storing the images in a database may result in better performance. "

    This is what you need to be careful with. A database table can only be 2GB in size.

    And from the replies:

    "I've dealt with cases in excess of 20K images, and have always found it easier to manage via the filesystem, with reference in the database. It required some rigid directory structure rules, but was definitely manageable."

    Can anyone elaborate on the rigid directory structure rules mentioned here?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •