2

What are the advantages and disadvantages of storing an image as a blob in the database vs storing just the file name in the database.

I'm using PHP(CodeIgniter) with MySQL.

I know this question is subjective but a client asked me this question and I couldn't give a good answer.

Sunny
  • 1,973
  • 2
  • 17
  • 22

3 Answers3

14

I'd generally say that :

  • database is harder to scale :
    • having a DB that has a size of several GB because of images will make many things (backup, for example) harder.
    • do you want to put more load on your DB servers, to serve... files ?
  • serving images from the filesystem is something that webservers do well
    • and it allows one to do some tasks (like generating thumbnails, for example) without involving the DB server
    • I suppose, if needed, that using plain old files will also make things easier the day you want to use some kind of CDN

Which means I would almost always store images / files in the filesystem, and only store the file's name (and, possibly, size and content-type -- that can always be useful) in the database.


Thinking about it, that question is probably a duplicate... Oh, yes, it actually is ; see at least those questions+answers :

Community
  • 1
  • 1
Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
  • Pascal MARTIN, Your answer seems pretty convincing. However, I must state that having tried out both methods, I didn't really see much of a difference in performance (i.e., in storing images externally, as opposed to keeping them on a DB as BLOBS). Perhaps my data volume wasn't much. Another concern is, are you suggesting that reading multiple BLOB images from a DB is slower than reading multiple files off the file system? I honestly cannot accept that but the only issue I see is that back ups maybe smaller if files were kept externally. – itsols Jun 13 '15 at 02:52
1

I doubt your client have enough experience to judge an answer.
I was asked once by customer to store image strictly into database. After some investigation I've discovered that they just meant "to store images on the server side", because they had an MS Access database before and it has a choice between storing images in the database or just links to the images, while image itself stayed at the users hard disk and often been unavailable.

As for your question, I doubt that image has any relation with relational model. You cannot sort, filter, relate with other rows based on blob content. While you have to do many things to support images in the database - dedicated script to show, emulate conditional get, etc. I know, this feature exists. But it's more an obvious attempt to follow the fashion than a real need, especially in the web development, where images being requested with separate requests.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
0

I prefer to rename images files when uploaded and store them into db row related to his own parent (user,product,) ... then upload them into 3 folder:

/uploads
  /img
    /products
     /small 
     /medium 
     /xxl

resizing them into small (50x50) , medium (90x90), xxl (original dimension), before moving them into these directories.

if you need SEO images you can store them for example:

id | product | image
1    book      1-book.png

so you'll get id and product name in same image file.

or you can even just store 

id | product | image
1    book      1.png

then is simple to attach src paths :

/*Config file*/
$config['base_static_products_url'] = 'uploads/img/products/';

/*View file*/

<img src="<?php echo base_url().$this->config->config['base_static_products_url'].'/small/'.$row->image ?>" alt=""/>

OR (no SEO)

<img src="<?php echo base_url().$this->config->config['base_static_products_url'].'/small/'.$row->id.'.png' ?>" alt=""/>
itsme
  • 48,972
  • 96
  • 224
  • 345
  • look, it depends also if you need to store all at same file ext the images , if you know you'll use only png files or just jpg files you can also NOT store anything in db and just rename files when uploaded with row_id.png – itsme Apr 18 '12 at 16:24