Is there a function in MySQL that will compress the results returned from a query ?

Question

I would like to compress the results if it reached a certain number of rows.

The client program would then uncompress the result and process.

This a desktop with a client/server architecture, some clients connects through vpn.

When a client connects, it will send data to the server. After the server has done processing, the client will then download updated data from the server.

The clients are located in several towns, with average of 50-100 kms. away. They will connect through internet using vpn. But that is the initial plan, not implemented yet, using vpn or other means.

Answer 1

There is the COMPRESS function which can be used to compress strings into binary strings. However, depending on your needs that may not really address the problem especially since database records are usually already very compact by their very nature so I'm not certain how much benefit you'll receive from attempting further compression of the result set.

One thing you need to be careful of is premature optimization. Oftentimes you can introduce unneeded complexity and actually hurt performance if you attempt to optimize prior to correctly identifying actual bottlenecks and not just shooting blindly at the possible ones.

In that vein I would ask if you have actually identified a bottleneck in your application? If so, where exactly is it and what is its nature? What environment are you working in? Is this web development or desktop with a client/server architecture?

Perhaps you can add some of this additional information to your question and then it would be possible to better assist you?

Answer 2

Most MySQL clients support compression (--compress option)

But are you sure you want to do this within MySQL? This sounds more like a task for a web or application server?

Most web servers support compressing responses with gzip etc

Have you got some more details to share on what the issue is and what platforms and tools you are using?

Answer 3

MySQL supports a compressed protocol, but depending on what tools you're using to connect to the database, how you utilize the protocol will be different.

Compression

Compression is used if both client and server support zlib compression, and the client requests compression.

A compressed packet header is: packet length (3 bytes), packet number (1 byte), and Uncompressed Packet Length (3 bytes). The Uncompressed Packet Length is the number of bytes in the original, uncompressed packet. If this is zero then the data is not compressed.

When the compressed protocol is in use (that is, when the client has requested it by setting the flag bit in the Client Authentication Packet and the server has accepted it), either the client or the server may compress packets. However, compression will not occur if the compressed length is greater than the original length. Thus, some packets will be compressed while other packets are not compressed.

(reference)

If you provide more information about how you are connecting, we might be able to supply you with additional information. For example, in the Connector/ODBC Connection parameters, there is a flag, that will "Use the compressed client/server protocol".