0

I read about storing blob data in tables a few years ago and thought it was a fantastic idea to store images for blog posts, but after reading the sheer number of negative points about the affect it has on page run time made me dismiss it.

I eventually created a php upload system for blobs and after looking at the data for several images I'd uploaded, I could see how a growing number of images could eventually cause a problem.

However, uploading small images (120px x 120px) produced quite a short number of characters in the blob data cell, about the same as a short news article that I would write for my sites newsfeed.

So, what is the deal with blob data? Does it really slow down database performance, and at what sort of size does it become a problem? I'd like to produce a small 120 x 120 thumbnail for each post, but don't particularly want to upload the physical file.

Wayne FA
  • 23
  • 5
  • Is an SQL database a file system? Can image data be queried? – Rafael Oct 11 '18 at 17:37
  • 1
    Possible duplicate of [Storing Images in DB - Yea or Nay?](https://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay) – Rafael Oct 11 '18 at 17:39

1 Answers1

1

The reason for keeping your images out of a dbms, no matter how small, is not because the image retrieval itself particularly slows down the database. Instead, it's because it makes your database and web app do extra, unnecessary, work.

When a user requests the image, your web app (php, or whatever language) must use a SELECT to retrieve the information from the database and then send it to the user's browser. That's extra work for the web app and the database. If you send a page with five images on it, your web app will have to handle six requests from the browser rather than one. That's the cause of the slowdown.

Ordinarily a web page with images has, in the browser, HTML looking something like this:

 <p>blah blah blah</p>
 <div><img src="/images/thumbnails/0045.jpg" width="120" height="120" /></div>
  (etc)

Your web app gets a request and assembles and delivers the page html. The web browser parses the page, renders it, and finds all the <img /> tags. The browser then issues another request to your web app for each image it finds. To handle those requests for images, your web app must handle a separate request from the browser for each image. When handling each of those requests, your code must then issue another SELECT request, receive the requested image from the dbms, and send it to the browser.

So, the SELECT you do to deliver the page doesn't need the image blob and can't use it. The SELECTs you do to retrieve the images probably can't use the columns containing html content.

Those extra requests to your web app are the problem caused by storing images in a table. (Also, most dbms systems including MySQL have to do a little bit of extra work to fetch BLOBs.)

And, your web app has to deal with the headers to control caching, content-type, and so on. That's an extra programming and testing burden.

Images, even thumbnails, are static. Web servers like apache and nginx are tremendously optimized to deliver them to browsers directly from your server's file system. (The servers memory-map the file, then just write that memory to the outbound HTTP socket with one send call.) And, if your app scales up you can use a content delivery network (like Cloudflare) to deliver the static images rapidly to large numbers of users.

You do need to manage your images in your file system, and tell your web server where to get them. The material you've read about images and databases has multiple suggestions for doing that.

If certain pages of your content have unchanging thumbnails in them, you might consider embedding the images directly in the pages' html using data urls.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks for the detailed answer. Just for clarification, the image will be stored in the same table as all other data for the article - `ID . Title . Subtitle . BLOB . article . date_added . author, etc, etc`. I plan on having 5-6 articles load up on a single page ( kind of like blogger ) - Is calling this number of SELECT results likely to affect the speed of the page, even without the BLOB? – Wayne FA Oct 11 '18 at 18:24
  • Hmmm. Okay, so let's suppose that each article ( table row ) uses 2 images ( one thumbnail, and one main large image ) which are each stored in 2 different locations. This means that Im going to be showing 10 or 12 images per page using the tag, e.g. `img src="uploads/thumbnails/ID.jpg"`. Is this a problem for server requests? – Wayne FA Oct 11 '18 at 18:49
  • No, if you deliver the images as static objects from your web server (not from your dbms) this should be fine. Web servers are built for this. – O. Jones Oct 11 '18 at 23:00
  • 1
    Great, thank you. I've started working on a folder structure to hold physical images rather than rely on a database to store date. – Wayne FA Oct 12 '18 at 07:35