Save gzip: edited text in mysql?

Question

Is it common for large applications and databases for GZIP text data before embedding them in a database?

I assume that any full-text search in a real text field will not work before unzipping it again?

Answer 1

I have not seen this much, since it basically does not allow doing any data manipulations on the MySQL side:

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

However, if you use your database only to store this data and do not manipulate it, it may be interesting.

Note. You might want to do some tests to measure the performance impact that this might have, since compression / decompression requires a processor!

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

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

Answer 2

If you are using the InnoDB table type in MySQL with one of the latest versions, then you can enable compression in the InnoDB table itself.

It was managed at a low level, so it does not change your requests or anything else. From what I read, the small overhead for compression is offset by a decrease in the IO of the disk and the ability to store more data in the buffer pool in memory. However, you specified a full-text search that InnoDB does not support, so this may not be an option.

There is also an Archive table type in MySQL, but you lose the indexing functionality except for the primary key, which I assume.

Another option is to "pack" the MyISAM table, but I believe that it makes the table read-only and does not compress, as well as other parameters.

Answer 3

Bad idea. Additional processing to save space when the disk space is less than 1 GB will not compensate for the extra programming time for this (not only initially, remember about maintenance).

This will slow down access to the database, because the data needs to be compressed / compressed. Indexes will not function properly on compressed data, since you will need to scan the table, unzip the data, and then compare. And full-text search is also missing.

If you must do this, do not use gzip. use the built-in COMPRESS function.