Mike Connell wrote:
> Could you, Jay, or someone else elaborate on how
> Prepared statements are beneficial in MySQL. I'm familiar with how
> this is beneficial in Oracle, but was not aware that MySQL had machinery to also
> take advantage.
Well, the general principle is similar to Oracle, I believe. The C API's prepared statement calls allow for faster execution of repeated SQL calls. It does this by initiating the SQL statement in the client, and passing this statement to the server, which "prepares" the statement for repeated execution by parsing the statement, and setting up placeholders for variables to be filled in. After the statement has been prepared, the client and server communicate to each other in a binary format, and only send the placeholder parameters back and forth between each other, instead of the whole query. This speeds things up considerably on repeated execution calls because the statement does not need to be parsed over and over; instead the placeholders are simply filled in with the next set of parameters as the client issues repeated calls against the server's prepared statement. It can also serve to reduce network traffic since the communication between the client and server is a) binary, and b) not sending as much info (only the new params...)
Unfortunately, I'm not a Java guy, and don't really know a whole lot about Connector/J and its abilities. Some preliminary research returned some inconsistent findings. In one section of the manual, it says that Connector/J was a candidate for prepared statements, but I could find no support for prepared statements in the Connector/J docs. The mysqli PHP library contains prepared statement execution for PHP, which it sounds like would be ideal for your front loader written in PHP. Perhaps Ingo might know about the Java implementation, or you might consider posting a question on the Connector/J forum.
Hope this helps a bit :)
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com