Prepared Statements, MySQL 4.1, and binary protocol
Posted by: Jason Winnebeck
Date: February 11, 2005 02:04PM

I've been trying to find out some more information about this, and it is mostly for my curiousity and I cannot find it on the forums or in the manual more precisely than I've seen so far.

I've heard that MySQL protocol normally sends all data across the wire (TCP in this case) in text encoding, and even does this when connecting to localhost.

It always seemed to me that this is very inefficent, because if the client wants the data in binary form the server converts it to text, sends it across the socket and then the client has to parse it back. Also when sending over the network, text data is larger, so more bandwidth is used.

Some of our DB operations are CPU bound. What I'm wondering is if the binary protocol actually sends data across in binary form, which is faster to parse and more compact to send across the network, so for slow CPUs or slow connections, the binary form will result in a speedup.

Is this true? If I understand correctly, the binary protocol can only be used in C or JDBC when using the new prepared statement API? So if I were to use prepared statements, MySQL 4.1.8 and Connector/J 3.1.6 then I will automagically get data across the network in binary form? I figure both server side preparation and binary-encoded results will speed things up when I make repetitive select queries. Is what I'm thinking correct? Would anyone like to make more of a comment about the implementation of the new prepared statements/binary encoding to satisfy my curiousity?
So far from my benchmarking, 3.1 is faster than 3.0 in general but uses a lot more memory. MySQL 4.1 seems to be slower than 4.0, but takes less memory (when not using server-side prep stmt).

Jason Winnebeck
Center for Integrated Manufacturing Studies
Rochester Institute of Technology, Rochester NY

Options: ReplyQuote

Written By
Prepared Statements, MySQL 4.1, and binary protocol
February 11, 2005 02:04PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.