Store GZIP:ed text in mysql?

Question

Hi!

Is it a common thing for bigger applications and databases to GZIP text data before inserting it to the database?

I'll guess that any full-text search on the actual text field will not be working before unzipping it again?

Thansks

Answer 1

Bad idea. Extra processing to save some space when disk space is less than $1 GB won't offset the extra programming time to do this (not just initially, remember maintenance).

This will likely make database accesses slower since the data needs to be decompressed/compressed. Indexes wont function properly on compressed data since you would need to do a table scan, decompress the data, then compare. And full text search is out as well.

If you must do this, please don't use gzip. use the built in COMPRESS function.

Answer 2

I've not seen this done much, as it basically prevents one from doing any manipulation on the data on the MySQL-side :

• no fulltext, yes
• but also no like, no =, no other manipulation...

Still, if you're using your database only to store that data, and not manipulate it, it might be interesting.

Note : you might want to do a few benchmarks, to measure the performance-impact this could have, as compression/decompression requires CPU !

After that, question is : will you deal with the compression on the client (PHP) side, or on the server (MySQL) side ?

In the second case, there is a COMPRESS() function, provided by MySQL, that might interest you.

Answer 3

If you are using the InnoDB table type in MySQL with one of the more recent versions then it's possible to enable compression on an InnoDB table itself.

It's managed at the low level so doesn't change your queries or anything. From what i've read, the slight overhead for compression is offset by reducing disk IO and allowing more data to be stored in the buffer pool in memory. You did however mention full-text search which InnoDB doesn't support, so this may not be an option.

There is also an Archive table type in MySQL but you lose indexing functionality apart from the primary key i believe.

Another alternative is to "pack" a MyISAM table but i believe that makes the table read only and doesn't compress as well as the other options.