Prepared statement performance in MySQL

While working on performance tuning for one of our clients, we have performed some benchmarks on the various statement types and JDBC options for MySQL. The results turned out to be somewhat interesting, so we are sharing it here :)

Definitions

Reused:

The prepared statement was reused in a loop (as opposed to opening/closing a new one for each query).

Server-side:

The JDBC option useServerPrepStmts was set to true.

Cached:

The JDBC option cachePrepStmts was set to true.

Duration:

Time to complete test in seconds.

Relative:

Elapsed time relative to the base-case (plain-statements). Lower is better.

The benchmark issued simple select queries, with randomized parameters that return no results. Tests were performed on MySQL 5.0.44, using MySQL Connector/J 5.0.7.

General conclusions

Plain statements are not worth the hassle or the potential SQL-injection security holes. Prepared statements perform significantly better with a combination of useServerPrepStmts=true and cachePrepStmts=true. And the conventional wisdom of reusing JDBC statements still applies.