DatabaseLink`: MySQL query with client/server compression

7

2

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.

JxB

Posted 2013-01-20T17:45:59.367

Reputation: 4 891

Answers

0

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"}]

JxB

Posted 2013-01-20T17:45:59.367

Reputation: 4 891

7

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]]]

Nick Stranniy

Posted 2013-01-20T17:45:59.367

Reputation: 1 213

Nick, your ``DeveloperRawUncompress 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 – 2013-02-02T19:58:22.893