Prepared Statements, MySQL 4.1, and binary protocol
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