What is the efficient way to store an image in database?

Question

what is the most efficient way to keep images in mySQL database system?

option 1 - Converting the image to BLOB data type and keep it in a seperate column

or

option 2 - saving the image in a separate folder and keep the file path in a relevant database column

Option2 is better way, Storing Image on File System is good. — AK47, Apr 08 '14 at 10:20
1. http://blog.sqlauthority.com/2007/12/13/sql-server-do-not-store-images-in-database-store-location-of-images-url/ — AK47, Apr 08 '14 at 10:23
Whenever asking a question ,please try to search for existing same question first, it will reduce duplications. — AK47, Apr 08 '14 at 10:26
Answers and comments provided are slightly misleading. If you save images to the db - it is slower, but you can replicate the image to multiple servers if you use replication (way more hassle than it sounds, but in theory it gives you certain reliability to fetch the image). The "rule" should be - if you run your whole website on 1 server, then the most efficient method is to store image path to db, and image to file system. If you are in a clustered environment, then saving images to database will probably be done completely different (as you wouldn't use MySQL for that probably). — N.B., Apr 08 '14 at 10:41
ok think like this situation. there are 100,000 user profiles and each profile containing an image file. each image has unique id which generate using database auto increment and image paths are stored in according to the id. if I want to find an image it wants to scan hole image folder sequencely. I think that it is very cost operation rather than buffering an image where in database — Roledenez, Apr 08 '14 at 16:28
possible duplicate of [Storing Images in DB - Yea or Nay?](http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay) — Mr. Radical, Apr 08 '14 at 23:41

score 5 · Answer 1 · edited May 23 '17 at 12:26

5

Go through Following links,

What is the most efficient way to store and access images
What is the most efficient way to store and access images
The most efficient way to store photo reference in a database

edited May 23 '17 at 12:26

Community

1
1

answered Apr 08 '14 at 10:25

AK47

3,707
3
17
36

score 3 · Accepted Answer · edited May 23 '17 at 10:33

3

option 2 for me because, for what I have read from other articles, converting the image to blob would make the process slower. unlike on storing the path you just retrieve it's path string to the database and retrieve much faster.

here are other related articles to that Storing Images in DB - Yea or Nay?

edited May 23 '17 at 10:33

Community

1
1

answered Apr 08 '14 at 10:23

unloki9

68
1
5

score 1 · Answer 3 · answered Apr 19 '21 at 22:58

For larger images you should use file system but for small images like thumbnails that's how i handle it with typeorm

@Column({ type: "longblob" })
thumbnail: string;

By the way you should run this commands in a mysql console connected to that same server to avoid any package size errors

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

and I converted image to base64 string with built-in FileReader API

// file param is coming from html input element
const reader = new FileReader();
// use try-catch because if user don't select a file it throws an error
    try {
      reader.onloadend = (e) => {
        const base64string = e.target.result;
        sendImageToServer(base64string);
        }
      }
      reader.readAsDataURL(file);
    } catch (error) {
      console.log(error);
    }

When you are sending thumbnail to client it converts it to a buffer object (ex. \

{
   type: "Buffer", 
   data: [
     // Uint8Array
   ]
}

so we need to convert it base64 string again

// This 'toString' function will convert buffer array to base64 string
const thumbnail = Buffer.from(recipe.thumbnail).toString();
sendDataToClient(thumbnail);

Then displaying image is very easy

// If you're using react like me you can do it like that 
<img src={base64stringFromServer} alt="thumbnail" />
// otherwise just select img tag with query selector then set 'src' attribute to 'base64stringFromServer
// and find a better variable name then this :)

I think that's the best way to store images at database.

score -1 · Answer 4 · answered Apr 08 '14 at 17:39

-1

I suggest this as an answer :- Store large images in the file system. Store small images (like thumbnails) in the database

answered Apr 08 '14 at 17:39

Roledenez

751
4
16
43

What is the efficient way to store an image in database?

4 Answers4