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.)
Advertisement
Answer
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 = "x1fx8bx08x00".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.