0

I'd like to know what's the best method to store images into a MySQL database with the best performance. (assume a big database)

Solution A: Use LONGBLOB type and put the binary content

Solution B: Store the files on the hosting and save the URL how a VARCHAR type

Thanks in advance

oscarvady
  • 450
  • 1
  • 4
  • 12

3 Answers3

2

I'd say rather than store the url, store the image name like "image1.png" and host the images on your server. That should help reduce db size and call times a bit. Then programmaticly prefix the url path and reference that.

Its probably also better to just save the file name in case the image location changes. that way you won't have to worry about updating the entire table and you can just fix the one line of code with the url path

sandman0615
  • 521
  • 6
  • 8
2

I tend to lean toward putting them in the database. That way when you do backups of the DB, it's all packaged up together. If you ever move servers, then you don't have to worry about moving files from the file system.

Unless you're talking about large files(GBs), i doubt you would see much of a difference from file system vs.db performance.

Either way is going to work for you, just a matter of preference and what works best for your particular situation.

There are many answers already on SO and the internet.
Storing Images in DB - Yea or Nay?

Community
  • 1
  • 1
Rick S
  • 6,476
  • 5
  • 29
  • 43
2

If you are building a system that needs to scale up to serve many images: there is an enormous disadvantage to putting the image contents in your DBMS.

Web servers can be clustered around large common file storage systems and can serve images very efficiently. This sort of file-serving architecture has been highly optimized by the various web server products like Apache, nginx and IIS.

But if you put your images in database BLOBs, fetching them from the database becomes a bottleneck. Plus you'll need a script (php, .net, whatever) to run to serve each image.

Almost all production web sites serve their images directly from files. You can store, in your DBMS, the locations on your file store, then convert them to URLs in the HTML pages you send out.

For example, if the imgloc column contains u/10234/abcde.img, and the table also has width and height columns, your web app can emit something like 

<img src="/content/u/10234/abcde.img" width="300" height="200">

Then the client will fetch the image from your content store.

O. Jones
  • 103,626
  • 17
  • 118
  • 172