optimization - Base64 MySQL storage space optimalisation

Question

I have plenty of base64_encoded strings which I save in my MySQL DB. However, since base64_encoded strings are 33% larger, I would like to know how I can optimize my DB storage (Now I store my strings in a LONGTEXT field, but what about LONGBLOB or something similar?). So, how I can save my data (which is now base64 encoded) more optimized so I save storage space... (When selecting the value, I still have to be able to encode it base64 safely again).

Thanks

Answer 1

If you have 5.6.1 or later, FROM/TO_BASE64() are available in MySQL.

Base64 is plain ascii text. Storing base64 in a TEXT is no problem. No escaping is necessary since (I think) there are no naughty characters. But storing the result of FROM_BASE64 needs a BLOB (of some size).

So...

INSERT INTO t (myblob) VALUES ( FROM_BASE64('UmljayBKYW1lcw==') );

SELECT TO_BASE64(myblob) FROM t;

The FROM_BASE64 will reconstruct the original (pre-base64) data.

Even better would be to compress instead:

INSERT INTO t (myblob) VALUES ( COMPRESS('UmljayBKYW1lcw==') );

SELECT UNCOMPRESS(myblob) FROM t;

That way, you will (probably) get more than the 33% _if the original (pre-base64) data was compressible. If the original was a .jpg or some other already-compressed data, then you may as well use FROM_BASE64 instead of COMPRESS.