5

I am aware that storing images as BLOBs in SQL is not advised. However, working both on my local PC and on a server makes it difficult to synchronize images between the two. Would there still be a reason not to use BLOBs solely as a backup, that would create a cached file locally (to serve statically)?

Essentially, is performance only an issue when the BLOB column is selected? If the only effect would be a larger table then I don't see a reason not to keep the image associated directly to the table entry.

nebkat
  • 8,445
  • 9
  • 41
  • 60
  • I know this doesn't directly answer but could you elaborate on why the images can't be shared? Could they be hosted on a server online (or CDN) ? – 1mike12 Oct 13 '15 at 17:36
  • 1
    @1mike12 Currently everything is stored locally and all paths are relative to the base directory/website url to make development in different environments simpler. It's a lot easier when testing to not have to worry about breaking the production site. Only the MySQL database is central. Maybe in the future I will switch to a centralized system for images too, but for now this is more a general SQL question partly out of curiosity. – nebkat Oct 13 '15 at 17:51
  • 2
    There is no good or bad practice to store images (files) into a BLOB field. It is rather requirement dependent. Biased based : I generally don't store images into the file system in large-scale Java EE applications (seriously, I was completely tired of doing so in the past) as they cannot be synchronized with respective database operations especially, when they are to be sent to a remote EE server. A little performance loss and/or a little database memory inefficiency is less important than correctness and completeness. Additionally, files stored into a database are more secure. – Tiny Oct 13 '15 at 18:03

1 Answers1

1

If you can make your images public - I would recommend saving the images on a different (and probably cheaper) storage than your DB, such as S3 for example.

But if your images should be private storing them in the DB is not the worst option but you need to handle them in your code.

If you are using mysql5.6 and Barracuda format (this is the most common this days), then from MySQL point of view - storing BLOB columns will have 2 data seeks on the disk instead of 1, as BLOB and TEXT columns are being saved out of the main data page.

You can read more info on this Percona Blog Post

Tata
  • 802
  • 9
  • 19