NodeJS MySQL blob upload is slow - bypass MySQL blob conversion process with NodeJS buffer

Question

I am uploading some images to a blob in MySQL but every time I upload it is very slow. I tried uploading the plain text vs. converting the text to a buffer before uploading and both ways seem like they are just as slow as the other but I have a feeling that converting is faster. I am using NodeJS and uploading with the fileReader result value on the client. Is there any way I can make uploading faster on the server? I am thinking that if I insert the buffer, the MySQL blob will be able to read that a buffer is being inserted and wont attempt to convert it like it would a string. Is there a way to bypass the conversion process of a MySQL blob so I can upload faster? ..Or does the conversion happen quickly and is it something else that is slowing down the insertion?

  if(req.body.product_images.length == 0) { 
    req.body.product_images = null;
  } else {
    req.body.product_images = req.body.product_images.join('***img_separator***'); //string upload
    req.body.product_images = Buffer.from(req.body.product_images, 'utf-8'); //buffer upload 
  }

Rick James · Accepted Answer · 2022-05-23 14:43:05Z

1

UTF-8?? An image is not characters; it is BLOB or BINARY, and needs to be handled differently. Perhaps the best way to pass an image around is via Base64.

If the image is used on a web page, don't put it in the database. Instead, store it in a file and store the URL to it in the database. Then build <img src=...> in the HTML to reference it at load time.

Opaque string

To look at it from the database perspective: You have an 'opaque' string of bytes. The db does not need to care what is in it, so you just want the most efficient way to insert/fetch. BLOB (or perhaps MEDIUMBLOB) is fine.

If using TEXT, CHARACTER SET ascii would be optimal and COLLATE ascii_bin would be optimal and appropriate for BASE64.

edited May 23, 2022 at 14:43

answered May 21, 2022 at 18:43

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Johnathan M Over a year ago

Thank you. Yes, I create the base64 string on the client and upload that to the server. I did not know if converting it to an array buffer to store as a blob would be more efficient inserting than inserting the string itself. In terms of the file, yes I plan on using a standard file to store the long string but I am trying to figure out the correct file type if you know. I am storing many images per file.

Rick James Over a year ago

@JohnathanM - "many images per file" -- How to you plan to split them apart? "many images per database table" -- one per row?

Johnathan M Over a year ago

I am concatenating each image together with a string. One set of images per row with the id in the db as a reference to the file where the images will be stored. Just not sure which type of file to use.

Rick James Over a year ago

@JohnathanM - The "concatenating" sounds like a mistake.

Rick James Over a year ago

@JohnathanM - I added to my answer.

|

Collectives™ on Stack Overflow

NodeJS MySQL blob upload is slow - bypass MySQL blob conversion process with NodeJS buffer

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related