Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I'm trying to reduce transfer time when accessing rows of a mysql table over a network, where each row has a lot of data. Is there a way to enable client-server compression when accessing a mysql database via ``DatabaseLink```?

As a workaround, I could request a column with COMPRESS(column_with_lots_of_data) in my SQLExecute query, but it is not clear how to uncompress the result on the Mathematica side.

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

The official documentation for MySQL Connector/J has the answer. Setting the useCompression connection property to true enables zlib compression between client and server.

You can set this property when creating a new resource name:

Needs["DatabaseLink`"]
DatabaseExplorer[]

From the menu, open a connection, click "New", then "Next", give the source a name, and select "MySQL(Connector/J)" as the type. Specify the host info, user, password, database, and when prompted for connection properties, click "Add Property", and set the property name to useCompression and the value to true.

I've seen speedups of 70% when transferring large amounts of data (your mileage may vary).

Alternatively, client/server compression can be set programmatically using WriteDataSource:

WriteDataSource["testSQL", "MySQL(Connector/J)", URL -> "main/test", 
    Username -> "user", Password -> "password", Location -> "System",
    "Properties"->{"useCompression"->"true"}]
share|improve this answer
add comment

You can decompress on the Mathematica side easily.

Compressed MySQL reply has the following format:

  • first four bytes are size of uncompressed data (lowest byte first)
  • the rest is the string compressed with deflate algorithm (zlib library)

Here is an example of a reply:

{10, 0, 0, 0, 120, 156, 243, 72, 205, 201, 201, 87, 240, 170, 112, 82, 4, 0, 19, 42, 3, 58}

To decompress it in Mathematica you can use Developer`RawUncompress function.

FromCharacterCode[Developer`RawUncompress[Drop[%, 4]]]
share|improve this answer
    
Nick, your ``Developer`RawUncompress` is useful, and I've been using it. But this afternoon I found the answer to the question on client/server compress in the official Connector/J docs. –  JxB Feb 2 '13 at 19:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.