Mysql - end to end compression

I'm using MySQL with Java (standard jdbc connection).

I know that I can enable connection compression (so that the data generated in Java is compressed before getting to MySQL server). There is also an option to enable InnoDB Row compression so that the data that reaches MySQL is then saved in a compressed state.

Question is - since when compressed data is received by MySQL from java client it needs to be decompressed and then again compressed in this case - is it possible to mitigate this? I just wanted to have it saved in compressed state and save CPU cycles.

You could compress the strings yourself using Java's Deflater class. Then store the bytes resulting from the compression in a binary column (e.g. VARBINARY, BLOB, LONGBLOB).

There's no need to use MySQL's compressed protocol or compressed columns if you store such pre-compressed data. It won't be compressed any further, it will just be wasted CPU cycles.

The disadvantage of storing strings you compress in the client is that you can't use SQL expressions to manipulate the original string. You can only store and fetch the compressed bytes verbatim.

Re your comment:

The closest thing is the JDBC URL property useCompression, but as you know this only applies to the networking protocol, not to storage.

A few things prevent the end-to-end compression that you want:

• MySQL's storage engine architecture. The network protocol is independent of storage engine. The compression algorithm used by a given storage engine might not be the same compression algorithm used in the protocol.

• SQL query result sets may be combined from joining multiple tables. Joined tables may use compression or not, and may even use different storage engines.

• Compression has always been more or less experimental in MySQL, and there isn't enough demand to implement all the code required to handle the combinations implied by the above cases. As the saying goes, pull requests welcome.