Re: 3.1.7 Not Using Server-Side Prep Stmt?
It looked fine, until I realized something... Yes, our SQL I believe is like so:
(SELECT ...) UNION (SELECT ...)
IIRC, there was a reason why we put the selects in parens. It might be the order by that we have decided not to use, because you can do this:
(SELECT * UNION SELECT *) ORDER BY x
which is different than:
SELECT * ORDER BY x UNION SELECT * ORDER BY x
I'm not at work this weekend, so I don't have access to our code. Now that I know that method exists, I am willing to trace through MySQL driver code to resolve my curiousity as to what exactly is causing the speedup, but we've repeated the test several times and it is consistent a quite significant speedup (46 to 35 sec to run sync) and a significant decrease in bytes sent and received, and all we do is append the param to the JDBC URL (it is read in from a file).
I haven't tested this against a "real" server, yet. As I said our server is a special case: it is an embeded machine that runs at 166mhz and the key buffer is 16k only, and disk is flash memory-based and can supply the CPU as fast as it needs it -- quite different from a "real" server which is usually I/O bound, and this is why I wanted to try binary encoding.
The thing that doesn't settle with me is that the 3.1 series always forced a server-side prepare when you did PreparedStatement until 3.1.7, and would throw an exception if the server did not support it, right? Which means that 3.1.6 should be the same as 3.1.7 with the emulation turned off, correct? When I came across that serverPrepare sync bug (fixed in 3.1.7), I saw exceptions coming up through the ServerPreparedStatement class, so we should have been using them.
Jason Winnebeck
Center for Integrated Manufacturing Studies
Rochester Institute of Technology, Rochester NY