Minimizing MySQL output with Compress() and by concatening results?

Question

Hi all. It is crucial that I transfer the least amount of data possible between server and client. Therefore I thought of using the mysql Compress() function. To get the max compression I also want to concatenate all my results in one large string (or several of max length allowed by MySql), to allow for similar results to be compressed, and then compress these/that string.

1st problem (concatenating mysql results):

SELECT name,age
FROM users

returns 10 results. I want to concatenate all these results in one strign on the form: name,age,name,age,name,age... and so on. Is this possible?

2nd problem (compressing the results from above)

When I have comstructed the concatenated string as above I want to compress it. If I do:

SELECT COMPRESS('myname');

then it just gives me as output the character '-' - sometimes it even returns unprintable characters. How do I get COMPRESS() to return a compressed printable string that I can trasnfer in ex ASCII encoding?

Answer 1

Depending on the client library you're working with (not all libraries support it), maybe you could use the MySQL Compressed Protocol ?

Here are a couple of links about it :

• When should I use MySQL compressed protocol?
• Is there a function in MySQL that will compress the results returned from a query ?
• Large result sets vs. compression protocol
• The MySQL compressed protocol

Answer 2

1. You might want to investigate a stored procedure which could build up a string internally and return that. By its nature, MySQL isn't going to turn a 'vertical' query result into a 'horizontal' one for you, that's not what a relational DBMS is for. That's more of a display issue. The sole exception is using group_concat() if you're doing some kind of join query with grouping.

2. COMPRESS() returns a binary string, so no surprise that you occasionally get unprintable characters - it's not intended to return something "readable". As well, it adds 4 bytes to the compressed string to represent the length of the uncompressed string, so compressing 5 bytes or less isn't going to save you anything.