MySQL Store Image BLOB Bad Practice Performance

Question

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.

Answer 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