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.)

Accepted 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 = "\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.

MySQL COMPRESS vs PHP gzcompress

Solution

Problem