Prepared statement performance in MySQL

November 11, 2007 – 3:02 pm by Viktor

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

Type	Reused	Server-side	Cached	Duration	Relative
Plain	n/a	n/a	n/a	283	100%
Prepared	-	-	-	328	116%
Prepared	Y	-	-	267	94%
Prepared	-	-	Y	302	107%
Prepared	-	Y	-	433	153%
Prepared	-	Y	Y	243	86%
Prepared	Y	Y	Y	216	76%

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.