MySQL COMPRESS vs PHP gzcompress

Question

I am developing a PHP application where large amounts of text needs to be stored in a MySQL database. Have come across PHP's gzcompress and MySQL's COMPRESS functions as possible ways of reducing the stored data size.

What is the difference, if any, between these two functions?

(My current thoughts are gzcompress seems more flexible in that it allows the compression level to be specified, whereas COMPRESS may be a bit simpler to implement and better decoupling? Performance is also a big consideration.)

Answer 1

The two methods are more or less the same thing, in fact you can mix them: compress in php and uncompress in MySQL and vice versa.

To compress in MySQL:

INSERT INTO table (data) VALUE(COMPRESS(data));

To compress in PHP:

$compressed_data = "\x1f\x8b\x08\x00".gzcompress($uncompressed_data);

To uncompress in MySQL:

SELECT UNCOMPRESS(data) FROM table;

To uncompress in PHP:

$uncompressed_data = gzuncompress(substr($compressed_data, 4));

Another option is to use MySQL table compression.

It only require configuration and then it is transparent.

Answer 2

This may be an old question, but it's important as a Google search destination. The results of MySQL's COMPRESS() vs PHP's gzcompress() are the same EXCEPT for MySQL puts a 4-byte header on the data, which indicates the uncompressed data length. You can easily ignore the first 4 bytes from MySQL's COMPRESS() and feed it to gzuncompress() and it will work, but you cannot take the results of PHP's gzcompress() and use MySQL's UNCOMPRESS() on it, unless you take specific care to add in that 4-byte length header, which of course requires having the uncompressed data already...