MySQL Compression – How to Compress Base64 Encoded Image in Database

compressioninnodbMySQLpercona

Database Info

  • Database Type – Percona MySQL.
  • Table engine – InnoDB.

I have a database table that is currently using 17GB at 400,000 rows. This size is mainly because I'm storing a 40kb base64 encoded image in each row. I'm wondering if anyone else has ran into the same type of problem and what they decided to do about it. The options I'm currently thinking about are:

  • Method 1 Storing each image named by the row's unique id in some sort of cloud hosting server. This would fix my database size problem, but it would be a pain to manage because the images would be on a different server. Also pushing the images up to the cloud server would take longer than the current method.
  • Method 2 Compressing just that column on a mysql database level? (if that's possible? I'm not sure but I believe it is, still researching. I would assume this would slow down the insert and select statements also though.)
  • Method 3 Compress the base64 before sending it over to the database with something like LZString Compression. (I'm sure this would entail on my database level such as what field type to put this data in or character set/encoding would be needed. Possibly could combine method 2 and 3 together for maximum savings? not sure if that would work or if it would be possible)

If anyone could give me some information on what would be the best way for me to approach this dilemma or if anyone has run into this problem before I would really appreciate the any help/info.

Thanks in advance.

Best Answer

Base64 is an encoding for transferring data reliably over the wire. It usually increases a files size so I wouldn't look to store a file in this format.

BLOB
The first thing I would do is decode the Base64 back to the original file and store it as a BLOB field. This could shrink the files by a third.

Table design
You said each row has a 40kb image. Why are they all the same size? If some rows share the same image then you could put the images in a table of their own and reference them from your big table.

Limit filesize
Most sites that let you upload an avatar limit the filesize/resolution of the image. Without knowing what these images are for perhaps you could impose a similar limit. You could even rescale the image automatically when they upload it.

Sorry if these are already obvious to you. It's difficult to be more specific without knowing the background about what the images are for.

Related Question