There are a lot of instances where it’s extremely convenient to blobs of data in a MySQL database. It’s not necessarily the most efficient storage, but it definitely has convenience points.
Speaking of convenience, MySQL actually includes compression functions that you can use right in your queries, allowing you to store the data compressed, but sill have introspection abilities without any special interface. Unfortunately, however, those functions aren’t directly compatible with, say, the PHP gzip extension, so you’re stuck packing all of the compression load on your perhaps already overloaded database.
That is, until now. A few days ago I was facing this exact problem at work, and with a little goading from the DBAs (i.e., they said it was “impossible”), I determined that I’d fix it, regardless of what it took.
After a few skinny dips into both the MySQL and gzip-extension source code, I discovered that MySQL was prepending a long integer onto the beginning of the compressed string. Luckily, this is easy to replicate in PHP:
<?php // compress compatible with MySQL UNCOMPRESS(...) $compressed = pack('L', strlen($string)).gzcompress($string); // uncompress compatible with MySQL COMPRESS(...) $uncompressed = gzuncompress(substr($compressed, 4)); ?>
Compression for all!